Bagaimana untuk melaksanakan kunci baris, kunci meja dan kebuntuan dalam mekanisme kunci Mysql

Apa itu kunci Mysql? Apakah jenis kunci yang ada?

Takrif kunci:
Sumber yang sama hanya boleh diakses oleh satu utas pada masa yang sama
Dalam pangkalan data, sebagai tambahan kepada pengkomputeran tradisional sumber (seperti CPU, Selain perbalahan untuk I/O, dsb.), data juga merupakan sumber yang dikongsi oleh ramai pengguna. Bagaimana untuk memastikan ketekalan dan keberkesanan akses serentak kepada data adalah masalah yang mesti diselesaikan oleh semua pangkalan data Konflik kunci juga merupakan faktor penting yang mempengaruhi prestasi akses serentak kepada pangkalan data.

Apa yang paling banyak digunakan oleh penguncian optimistik ialah rekod versi data untuk mencerminkan versi , yang sebenarnya merupakan pengecam.

Contohnya: update test set a=a-1 where id=100 and a> 0; Versi yang sepadan ialah medan Ia tidak semestinya memerlukan medan yang dipanggil versi Pada masa yang sama, medan ini akan dicetuskan syarat ini dipenuhi.

Klasifikasi kunci:
Daripada klasifikasi jenis operasi data (baca atau tulis)
Kunci baca ( Kunci kongsi): Untuk data yang sama, berbilang operasi baca boleh dilakukan pada masa yang sama tanpa menjejaskan satu sama lain.
Kunci tulis (kunci eksklusif): Ia akan menyekat kunci tulis lain dan kunci baca sebelum operasi tulis semasa selesai.

Daripada klasifikasi butiran operasi data
Kunci peringkat jadual: Kunci peringkat jadual ialah kunci dengan butiran terbesar dalam MySQL, yang bermaksud bahawa keseluruhan jadual semasa operasi ditambah Kunci (enjin MyISAM lalai kepada kunci peringkat meja dan hanya menyokong kunci peringkat meja). Contohnya, jika anda mengemas kini sekeping data dalam 100,000 jadual, transaksi lain akan dikecualikan sebelum kemas kini ini melakukan transaksi dan butirannya sangat besar.
Kunci peringkat baris: Kunci peringkat baris ialah kunci paling berbutir dalam Mysql, yang bermaksud bahawa hanya baris yang dikendalikan pada masa ini dikunci ( dilaksanakan berdasarkan indeks, jadi sebaik sahaja kunci tertentu dikunci dikunci Jika operasi tidak menggunakan indeks, maka kunci akan merosot menjadi kunci jadual)
Kunci peringkat halaman: Kunci peringkat halaman ialah kunci dalam MySQL dengan butiran penguncian antara kunci peringkat baris dan kunci peringkat jadual. Ia adalah kunci satu kali Sekumpulan rekod bersebelahan

Pengedaran dari perspektif serentak - sebenarnya penguncian optimistik dan penguncian pesimis hanyalah idea
Pesimis mengunci: untuk data yang disekat oleh dunia luar (termasuk sistem ini Ia memerlukan sikap konservatif (pesimis) terhadap pengubahsuaian kepada urus niaga semasa yang lain, serta transaksi daripada sistem luaran, dan oleh itu menyimpan data dalam keadaan terkunci sepanjang keseluruhan proses pemprosesan data.
Kunci optimistik: Kunci optimis menganggap bahawa data tidak akan menyebabkan konflik dalam keadaan biasa, jadi apabila data diserahkan untuk kemas kini, konflik data akan dikesan secara rasmi Jika konflik ditemui, mesej ralat akan dikembalikan . Cuba semula perniagaan

Kunci lain:
Kunci jurang: Dalam pertanyaan bersyarat, seperti: di mana id>100, InnoDB akan memberikan indeks rekod data sedia ada yang memenuhi syarat Penguncian item; untuk rekod yang nilai kuncinya berada dalam julat keadaan tetapi tidak wujud, ia dipanggil "jurang". kunci (IS) Dan kunci eksklusif niat (IX), tujuan kunci niat adalah untuk menunjukkan bahawa transaksi sedang atau akan mengunci baris dalam jadual

2. Perbezaan antara kunci baris dan kunci meja

Kunci peringkat jadual ialah kunci dengan butiran penguncian terbesar dalam MySQL , yang bermaksud mengunci keseluruhan jadual operasi semasa Ia mudah dilaksanakan. MYISAM dan INNODB yang paling biasa digunakan menyokong penguncian peringkat meja. Ciri-ciri:
Overhead rendah, penguncian cepat; tiada jalan buntu;

Kunci peringkat baris ialah kunci yang paling halus dalam Mysql, yang bermaksud bahawa hanya baris operasi semasa dikunci. Kunci peringkat baris boleh mengurangkan konflik dalam operasi pangkalan data. Butiran pengunciannya adalah yang terkecil, tetapi overhed pengunciannya juga yang terbesar. Ciri: overhed tinggi, penguncian perlahan mungkin berlaku; butiran penguncian adalah yang paling kecil, kebarangkalian konflik kunci adalah yang paling rendah, dan konkurensi adalah yang tertinggi
Penggunaan: penguncian baris InnoDB dicapai dengan indeks penguncian item pada indeks Untuk mencapai ini, InnoDB akan menggunakan kunci peringkat baris hanya jika
mendapatkan semula data melalui keadaan indeks Jika tidak, InnoDB akan menggunakan kunci jadual

Dalam pernyataan kemas kini berikut, b ialah. medan umum dan bukan lajur indeks , maka kunci peringkat baris akan ditukar kepada kunci peringkat jadual pada masa ini.

update from test set a=100 where b='100';

Sekarang mari kita ambil contoh praktikal untuk melihat cara innnodb menggunakan kunci baris.

Data dalam jadual semasa:

Mula-mula buka dua tetingkap sesi, dan kemudian tetapkan tahap transaksi mysql kepada tahap tidak komited:

Tetingkap sesi satu:

Tetingkap sesi dua:

 其中会话2的update一直都在Running中，一直到超时结束，或者会话1提交事务后才会Running结束。

可以通过show VARIABLES like "%innodb_lock_wait_timeout%" 查询当前mysql设置的锁超时时间,默认是50秒。 

可以通过set innodb_lock_wait_timeout = 60; 设置锁的超时时间。

只有在第一个会话提交后，第二个会话的更新语句才能成功执行。这代表了innodb用了锁。

那怎么确定是用了行锁呢？

 

 总结:会话一更新id=125的时候，给这条数据add lock了，那么在会话2中再次更新id=125的时候，这条数据是locked中的。这个lock加的是id=125这条记录。证明默认情况下id=125这条记录会加上行锁，除了这条记录之外的其它记录都可以成功地操作。

三、InnoDB死锁概念和死锁案例

发生死锁是因为多个事务相互持有和请求锁，并形成了一个循环依赖关系。多个事务同时锁定同一个资源时，也会产生死锁。在一个事务系统中，死锁是确切存在并且是不能完全避免的。

自动检测事务死锁并回滚一个事务，同时返回错误信息的功能由InnoDB自动实现。它根据某种机制来选择那个最简单（代价最小）的事务来进行回滚

死锁场景一之select for update:

产生场景：两个transaction都有两个select for update，transaction a先锁记录1，再锁记录2；而transaction b先锁记录2，再锁记录1

写锁：for update，读锁：for my share mode show engine innodb status

验证下死锁的场景:

 第一步更新会话一:

start TRANSACTION;
select * from wnn_test where a=199 for update;

第二步更新会话二:

start TRANSACTION;
select * from wnn_test where a=101 for update;

第三步更新会话一:

select * from wnn_test where a=101 for update;

第四步更新会话二;

select * from wnn_test where a=199 for update;

在更新到第三步和第四步的时候，已经发生了死锁。

来看下执行的日志:

show engine innodb status;最后一个锁的时间，锁的表，引起锁的语句。其中session1被锁 14秒(ACTIVE 14)，session 2被锁了10秒(Active 10)

死锁场景二之两个update

产生场景：两个transaction都有两个update，transaction a先更新记录1，再更新记录2；而transaction b先更新记录2，再更新记录1

 产生日志：

 注意：仔细查看上面2个例子可以发现一个现象，当2条资源锁住后，再执行第三个会执行成功，但是第四个会提示死锁。在mysql5.7中，执行第三个的时候就会一直在Running状态了，本博文使用的是mysql8.0 ，其中 有这个参数 innodb_deadlock_detect 可以用于控制 InnoDB 是否执行死锁检测，当启用了死锁检测时（默认设置），InnoDB 自动执行事务的死锁检测，并且回滚一个或多个事务以解决死锁。InnoDB 尝试回滚更小的事务，事务的大小由它所插入、更新或者删除的数据行数决定。

 那么这个innodb_deadlock_detect参数，到底要不要启用呢？

对于高并发的系统，当大量线程等待同一个锁时，死锁检测可能会导致性能的下降。此时，如果禁用死锁检测，而改为依靠参数 innodb_lock_wait_timeout 执行发生死锁时的事务回滚可能会更加高效。
通常来说，应该启用死锁检测，并且在应用程序中尽量避免产生死锁，同时对死锁进行相应的处理，例如重新开始事务。

Hanya apabila anda pasti bahawa pengesanan jalan buntu menjejaskan prestasi sistem dan melumpuhkan pengesanan jalan buntu tidak akan mempunyai kesan negatif, anda boleh cuba mematikan pilihan innodb_deadlock_detect. Selain itu, jika pengesanan jalan buntu InnoDB dilumpuhkan, nilai parameter innodb_lock_wait_timeout perlu dilaraskan untuk memenuhi keperluan sebenar.

4. Bagaimana untuk mengelakkan kebuntuan semasa pembangunan program

Intipati penguncian ialah sumber bersaing antara satu sama lain dan menunggu antara satu sama lain Selalunya dua (atau lagi) Sesi dikunci. Urutannya tidak konsisten

Cara mengelakkan dengan berkesan:

Dalam program, apabila mengendalikan berbilang jadual, cuba untuk aksesnya dalam susunan yang sama (elakkan Membentuk gelung menunggu)

Apabila mengendalikan data jadual tunggal dalam kelompok, susun data dahulu (untuk mengelak daripada membentuk gelung menunggu) Id utas: 1 , 10, 20 ditambah mengikut urutan Kunci B id benang: 20,10,1 Ini mudah dikunci.

Jika boleh, tukar urus niaga besar kepada urus niaga kecil, atau malah jangan buka urus niaga pilih untuk kemas kini==>insert==>kemas kini = masukkan ke dalam kemas kini pada kunci pendua

Untuk mengelakkan kunci meja, disyorkan untuk menggunakan indeks untuk mengakses data sebanyak mungkin dan mengelakkan operasi tanpa syarat, kerana menggunakan indeks boleh merekod kunci baris tanpa menyebabkan kunci meja

Gunakan pertanyaan nilai yang sama dan bukannya pertanyaan julat untuk menanya data, tekan rekod dan elakkan kesan kunci jurang pada nilai yang sama 1, 10, 20 di mana id dalam (1,10,20 ) pertanyaan julat id>1 dan id

Elakkan menjalankan berbilang skrip yang membaca dan menulis jadual yang sama pada masa yang sama Beri perhatian khusus kepada penyata yang mengunci dan mengendalikan sejumlah besar data; kami sering mempunyai beberapa skrip pemasaan , untuk mengelakkannya berjalan pada masa yang sama

Atas ialah kandungan terperinci Bagaimana untuk melaksanakan kunci baris, kunci meja dan kebuntuan dalam mekanisme kunci Mysql. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!