Apakah kunci dan klasifikasi MySQL?

1. Senario konkurensi pangkalan data

Dalam senario konkurensi tinggi, tanpa mengambil kira perisian tengah lain, senario berikut akan wujud dalam pangkalan data:

• Baca: Tiada masalah dan tiada kawalan serentak diperlukan.

• Baca dan tulis: Terdapat isu keselamatan rangkaian, yang boleh menyebabkan isu pengasingan transaksi dan mungkin menghadapi bacaan kotor, bacaan hantu dan bacaan tidak boleh berulang.

• Tulis: Terdapat isu keselamatan rangkaian dan mungkin terdapat isu kehilangan kemas kini, seperti jenis kemas kini pertama hilang dan jenis kemas kini kedua sedang hilang.

Sebagai tindak balas kepada masalah di atas, standard SQL menetapkan bahawa masalah yang berbeza mungkin berlaku di bawah tahap pengasingan yang berbeza:

Empat tahap pengasingan utama MySQL:

隔离级别	脏读	不可重复读	幻读
READ UNCOMMITTED：未提交读	可能发生	可能发生	可能发生
READ COMMITTED：已提交读	解决	可能发生	可能发生
REPEATABLE READ：可重复读	解决	解决	可能发生
SERIALIZABLE：可串行化	解决	解决	解决

Seperti yang anda lihat, MySQL sebenarnya menyelesaikan masalah tidak boleh berulang pada tahap pengasingan REPEATABLE READ Pada asasnya menyelesaikan masalah bacaan hantu, tetapi bacaan hantu masih wujud dalam kes yang melampau.

Jadi apakah penyelesaiannya? Secara umumnya, terdapat dua penyelesaian:

1️⃣ MVCC untuk operasi baca, mengunci untuk operasi tulis

Untuk bacaan , di bawah MVCC peringkat RR , apabila transaksi dimulakan, ReadView akan dijana, dan kemudian versi sejarah yang memenuhi syarat akan ditemui melalui ReadView Versi ini dibina daripada log buat asal Apabila ReadView dijana, syot kilat sebenarnya dijana, jadi di kali ini Pertanyaan SELECT juga dibaca syot kilat (atau bacaan yang konsisten Kami tahu bahawa di bawah RR, hanya operasi SELECT pertama yang dilakukan semasa pelaksanaan transaksi akan menjana ReadView , operasi SELECT seterusnya akan menggunakan semula ReadView ini, sekali gus mengelakkan masalah bacaan tidak berulang dan bacaan hantu pada tahap yang besar.

Untuk menulis, kerana tiada operasi kunci dilakukan pada mana-mana rekod dalam jadual semasa bacaan syot kilat atau bacaan konsisten dan transaksi ReadView ialah versi sejarah, untuk menulis Versi terkini operasi tidak bercanggah, jadi transaksi lain bebas untuk membuat perubahan pada rekod dalam jadual.

2️⃣ Operasi baca dan tulis dikunci

Jika sesetengah senario perniagaan kami tidak membenarkan membaca versi lama rekod, tetapi kami mesti membaca rekod setiap kali versi, sebagai contoh, dalam transaksi deposit bank, anda perlu terlebih dahulu membaca baki akaun , kemudian menambahnya pada jumlah deposit ini , dan akhirnya Tulis ke pangkalan data . Selepas membaca baki akaun, anda tidak mahu transaksi lain mengakses baki Hanya sehingga transaksi deposit selesai barulah transaksi lain mengakses baki akaun. Dengan cara ini, rekod perlu dikunci semasa membacanya, yang bermaksud bahawa operasi baca dan operasi tulis turut beratur dan dilaksanakan seperti operasi tulis-tulis.

Untuk bacaan kotor , ia adalah kerana urus niaga semasa membaca rekod yang ditulis oleh transaksi lain yang tidak terikat, tetapi jika transaksi lain sedang menulis rekod Apabila rekod dikunci , maka transaksi semasa tidak boleh terus membaca rekod, jadi tidak akan ada masalah bacaan kotor.

Untuk bacaan tidak boleh berulang, ia adalah kerana transaksi semasa membaca rekod terlebih dahulu, dan selepas transaksi lain membuat perubahan pada rekod dan melakukannya, transaksi semasa akan mendapat hasil yang berbeza apabila ia membaca semula nilai, jika rekod dikunci apabila urus niaga semasa membaca rekod , maka transaksi lain tidak boleh mengubah suai rekod dan secara semula jadi bacaan tidak berulang tidak akan berlaku.

Untuk bacaan hantu, ini adalah kerana transaksi semasa membaca rekod dalam julat, dan kemudian urus niaga lain memasukkan rekod baharu ke dalam julat, apabila urus niaga semasa membaca rekod dalam julat ini sekali lagi, ia menemui rekod baharu yang baru dimasukkan, kami memanggil rekod yang baru dimasukkan itu sebagai rekod hantu.

Bagaimana untuk memahami julat ini? Seperti berikut:

• Andaikan hanya terdapat satu bahagian id=1 data dalam pengguna jadual.

• Apabila transaksi A melaksanakan operasi pertanyaan id = 1, data boleh disoal Jika ia adalah pertanyaan julat, seperti id in(1,2), hanya satu keping data akan ditanya .

• Pada masa ini, transaksi B melaksanakan id = 2 operasi baharu dan melaksanakannya.

• Pada masa ini, apabila transaksi A melaksanakan pertanyaan id in(1,2) sekali lagi, 2 rekod akan dibaca, dengan itu bacaan hantu berlaku.

Nota: Disebabkan kebolehbacaan berulang RR, rekod id = 2 sebenarnya tidak dapat ditemui, jadi jika anda melaksanakan update ... where id = 2 sekali, pergi ke Anda boleh mengetahuinya dengan pertanyaan julat.

Bukan mudah untuk menyelesaikan masalah bacaan hantu dengan mengunci, kerana rekod hantu tidak wujud apabila transaksi semasa membaca rekod untuk kali pertama, jadi agak menyusahkan untuk dikunci ketika membaca , sebab tak tahu nak kunci siapa.

Jadi bagaimanakah InnoDB menyelesaikannya? Mari kita lihat dahulu apa yang mengunci enjin storan InnoDB.

2. Kunci dan klasifikasi dalam MySQL

Dalam dokumentasi rasmi MySQL, enjin storan InnoDB memperkenalkan jenis kunci berikut:

Begitu juga, ia kelihatan masih mengelirukan, tetapi kita boleh mengklasifikasikannya mengikut cara pembelajaran kunci dalam JDK:

3 Klasifikasi kunci berbutir

Apakah kebutiran kunci? Butiran kunci yang dipanggil merujuk kepada skop perkara yang anda ingin kunci.

Sebagai contoh, jika anda pergi ke bilik mandi di rumah, anda hanya perlu mengunci bilik mandi anda tidak perlu mengunci seluruh rumah untuk menghalang ahli keluarga daripada masuk.

Apakah butiran penguncian yang munasabah?

Malah, bilik air bukan sahaja digunakan untuk ke tandas, tetapi juga untuk mandi dan mencuci tangan. Ini melibatkan isu mengoptimumkan butiran penguncian.

Apabila anda mandi di bilik air, orang lain sebenarnya boleh masuk dan membasuh tangan mereka pada masa yang sama, asalkan mereka terpencil jika tandas, tab mandi dan singki semuanya berasingan dan agak bebas (basah dan kering dipisahkan, ia adalah ), sebenarnya, bilik mandi boleh digunakan oleh tiga orang pada masa yang sama, sudah tentu, tiga orang itu tidak boleh melakukan perkara yang sama. Ini memperhalusi butiran kunci Anda hanya perlu menutup pintu bilik mandi semasa mandi, dan orang lain masih boleh masuk dan mencuci tangan mereka. Jika kawasan berfungsi yang berbeza tidak dipisahkan semasa mereka bentuk bilik mandi, sumber bilik mandi tidak boleh dimaksimumkan.

Begitu juga, terdapat juga butiran kunci dalam MySQL. Biasanya dibahagikan kepada tiga jenis, kunci baris, kunci meja dan kunci halaman .

3.1 Kunci baris

Dalam pengenalan kunci kongsi dan kunci eksklusif, ia sebenarnya direkodkan untuk baris tertentu, jadi ia juga boleh dipanggil kunci baris.

Mengunci rekod hanya mempengaruhi rekod ini, jadi butiran penguncian kunci baris adalah yang terbaik dalam MySQL. Kunci lalai enjin storan InnoDB ialah kunci baris.

Ia mempunyai ciri-ciri berikut:

1. Kebarangkalian terendah konflik kunci dan konkurensi tinggi

   Disebabkan butiran kecil daripada kunci baris, Oleh itu, kebarangkalian pertikaian sumber kunci juga adalah minimum, jadi kebarangkalian konflik kunci adalah rendah dan konkurensi adalah lebih tinggi.

2. Overhed tinggi dan penguncian perlahan

   Kunci sangat memakan prestasi Cuba bayangkan, jika beberapa keping data dalam pangkalan data dikunci, Ia pasti akan menduduki banyak sumber, dan untuk mengunci, anda perlu menunggu kunci sebelumnya dilepaskan sebelum mengunci.

3. akan menghasilkan kebuntuan

   Untuk apa itu kebuntuan, anda boleh baca di bawah.

3.2 Kunci meja

Kunci peringkat meja ialah kunci peringkat meja, yang akan mengunci seluruh meja, yang boleh mengelakkan kebuntuan sangat Ia juga merupakan mekanisme penguncian yang paling terperinci dalam MySQL.

Kunci lalai enjin storan MyISAM ialah kunci meja.

Ia mempunyai ciri-ciri berikut:

1. Overhead rendah dan mengunci pantas

   Oleh kerana keseluruhan meja dikunci, Kelajuan mestilah lebih pantas daripada mengunci sekeping data.

2. Tidak akan ada jalan buntu

   telah mengunci seluruh meja urus niaga lain tidak boleh mendapatkan kunci sama sekali, jadi sudah tentu mereka tidak akan melakukannya Kebuntuan berlaku.

3. Kebutiran kunci adalah besar, kebarangkalian konflik kunci adalah tinggi dan keselarasan adalah rendah

3.3 Kunci halaman

Penguncian peringkat halaman ialah tahap penguncian unik dalam MySQL dan tidak biasa dalam perisian pengurusan pangkalan data yang lain.

Kebutiran kunci peringkat halaman adalah antara kunci peringkat baris dan kunci peringkat meja, jadi overhed sumber yang diperlukan untuk mendapatkan kunci dan keupayaan pemprosesan serentak yang boleh mereka sediakan juga adalah antara dua di atas . Selain itu, kunci peringkat halaman, seperti kunci peringkat baris, boleh menyebabkan kebuntuan.

	行锁	表锁	页锁
锁的粒度	小	大	两者之间
加锁效率	慢	快	两者之间
冲突概率	低	高	-
并发性能	高	低	一般
性能开销	大	小	两者之间
是否死锁	是	否	是

4. Klasifikasi keserasian kunci

Dalam MySQL, bacaan data terbahagi terutamanya kepada bacaan semasa dan bacaan syot kilat:

• Bacaan syot kilat

  Bacaan syot kilat dibaca data syot kilat PILIHAN biasa tanpa kunci ialah syot kilat dibaca.

  SELECT * FROM table WHERE ...

• Bacaan semasa

  Bacaan semasa bermaksud membaca data terkini, bukan data sejarah, kunci SELECT atau menambah , memadam atau mengubah suai data akan melaksanakan bacaan semasa.

  SELECT * FROM table LOCK IN SHARE MODE;
  SELECT FROM table FOR UPDATE;
  INSERT INTO table values ...
  DELETE FROM table WHERE ...
  UPDATE table SET ...

Dalam kebanyakan kes, kami mengendalikan pangkalan data berdasarkan bacaan semasa, dan dalam senario serentak, kami mesti membenarkan baca- Bacaan situasi tidak terjejas, tetapi jika operasi dalam situasi tulis-tulis, baca-tulis atau baca-baca menyekat satu sama lain, anda perlu menggunakan kunci kongsi dan eksklusif dalam Kunci MySQL.

4.1 Kunci kongsi dan kunci eksklusif

Kunci kongsi (Kunci Dikongsi) juga boleh dipanggil kunci baca, atau singkatan S kunci . Data boleh dibaca serentak, tetapi tiada transaksi boleh mengubah suai data.

Kunci Eksklusif (Kunci Eksklusif), juga boleh dipanggil kunci eksklusif atau kunci tulis, dirujuk sebagai Kunci X. Jika sesuatu menambah kunci eksklusif pada baris, hanya transaksi ini boleh membaca dan menulisnya Sebelum tamat transaksi ini, urus niaga lain tidak boleh menambah sebarang kunci padanya lepaskan.

Mari kita analisa situasi memperoleh kunci: Jika terdapat transaksi A dan transaksi B

• Transaksi A memperoleh kunci S rekod, dan transaksi B juga mahu untuk memperolehnya pada masa ini Jika rekod mempunyai kunci S, transaksi B juga boleh memperoleh kunci itu, maksudnya, transaksi A dan transaksi B memegang kunci S rekod pada masa yang sama.

• Jika transaksi B ingin memperoleh kunci X rekod, operasi ini akan disekat sehingga kunci S dilepaskan selepas transaksi A dilakukan.

• Jika transaksi A mula-mula memperoleh kunci X, sama ada transaksi B mahu memperoleh kunci S atau kunci X rekod, ia akan disekat sehingga transaksi A dilakukan.

Oleh itu, kita boleh mengatakan bahawa kunci S dan kunci S adalah serasi, kunci S dan kunci X tidak serasi, dan kunci X dan kunci X juga tidak serasi.

4.2 Kunci Niat

Kunci Dikongsi Niat (Kunci Dikongsi Niat), dirujuk sebagai IS Lock. Apabila transaksi akan menambah kunci S pada rekod, ia perlu terlebih dahulu menambah kunci IS pada tahap jadual.

Kunci Eksklusif Niat (Kunci Eksklusif Niat), dirujuk sebagai Kunci IX. Apabila transaksi akan menambah kunci X pada rekod, ia perlu terlebih dahulu menambah kunci IX pada tahap jadual.

Kunci niat ialah kunci peringkat meja Ia dicadangkan hanya untuk menentukan dengan cepat sama ada rekod dalam jadual adalah apabila kunci S peringkat jadual dan kunci X adalah. ditambah kemudiannya dikunci untuk mengelak daripada merentasi untuk melihat jika terdapat sebarang rekod terkunci dalam jadual. Maksudnya, kunci IS serasi dengan kunci IS dan kunci IX serasi dengan kunci IX.

Mengapa kita memerlukan kunci niat?

Kunci niat InnoDB digunakan terutamanya apabila berbilang kunci butiran wujud bersama. Sebagai contoh, transaksi A ingin menambah kunci S pada jadual Jika baris dalam jadual telah ditambahkan pada kunci X oleh transaksi B, maka aplikasi untuk kunci itu juga harus disekat. Jika terdapat banyak data dalam jadual, overhed untuk menyemak bendera kunci baris demi baris akan menjadi sangat besar dan prestasi sistem akan terjejas.

Sebagai contoh, jika terdapat 100 juta rekod dalam jadual, dan transaksi A mempunyai kunci baris pada beberapa rekod, maka transaksi B perlu menambah kunci peringkat jadual pada jadual jika tiada niat kunci, Kemudian anda perlu mengetahui dalam jadual sama ada 100 juta rekod ini dikunci. Jika terdapat kunci niat, maka jika transaksi A menambah kunci niat dan kemudian kunci X sebelum mengemas kini rekod, transaksi B terlebih dahulu menyemak sama ada terdapat kunci niat di atas meja dan sama ada kunci niat sedia ada bercanggah dengan kunci yang dirancangnya. untuk menambah. Jika terdapat konflik, tunggu sehingga transaksi A dikeluarkan tanpa menyemak setiap rekod. Transaksi B tidak perlu mengetahui dengan tepat baris mana yang dikunci semasa mengemas kini jadual, ia hanya perlu mengetahui bahawa sekurang-kurangnya satu baris telah dikunci.

Secara terus terang, fungsi utama kunci niat adalah untuk menangani percanggahan antara kunci baris dan kunci meja Ia boleh memaparkanbahawa transaksi memegang kunci pada baris tertentu, atau sedang bersiap untuk memegang kunci.

Keserasian pelbagai kunci pada aras meja :

thead> td>Tidak serasi

	S	IS	X	IX
S	兼容	兼容	不兼容	不兼容
IS	兼容	兼容	不兼容	不兼容
X	不兼容	不兼容	不兼容	不兼容
IS	兼容	兼容	不兼容	不兼容

S

IS

X

IX

S

Serasi

Serasi

Tidak Serasi

Tidak Serasi td>

ADALAH

Serasi

Serasi

Tidak Serasi

X

Tidak serasi

Tidak serasi

Tidak serasi

Tidak serasi

ADALAH

Serasi

Serasi td>

Tidak Serasi

Tidak Serasi

4.3 Kunci untuk operasi baca

Untuk operasi baca MySQL, terdapat dua cara untuk mengunci.

1️⃣ PILIH * DARI KUNCI jadual DALAM MOD KONGSI

Jika transaksi semasa melaksanakan penyataan ini, ia akan menambah kunci S pada rekod baca , yang membenarkan urus niaga lain untuk terus memperoleh kunci S pada rekod ini (contohnya, urus niaga lain juga menggunakan penyata SELECT ... LOCK IN SHARE MODE untuk membaca rekod ini), tetapi tidak boleh memperoleh kunci X pada rekod ini (contohnya, menggunakan penyata SELECT ... FOR UPDATE untuk membaca rekod ini, atau mengubah suai rekod ini secara langsung).

Jika transaksi lain ingin memperoleh kunci X rekod ini, mereka akan menyekat sehingga kunci S pada rekod ini dikeluarkan selepas transaksi semasa dilakukan

2️⃣ PILIH DARI jadual UNTUK KEMASKINI

Jika transaksi semasa melaksanakan penyata ini, ia akan menambahkan kunci X pada rekod baca, yang tidak akan membenarkan transaksi lain mendapatkan kunci S ini (untuk contoh, transaksi lain menggunakan pernyataan SELECT ... LOCK IN SHARE MODE untuk membaca rekod ini), dan kunci X tidak dibenarkan untuk mendapatkan rekod ini (contohnya, gunakan pernyataan SELECT ... FOR UPDATE untuk membaca rekod ini atau mengubah suai rekod ini secara langsung).

Jika transaksi lain ingin memperoleh kunci S atau kunci X bagi rekod ini, mereka akan menyekat sehingga kunci X pada rekod ini dikeluarkan selepas transaksi semasa dilakukan.

4.4 Kunci untuk operasi tulis

PADAM, KEMASKINI dan INSERT ialah operasi tulis biasa dalam MySQL. Penguncian tersirat, penguncian automatik dan buka kunci.

1️⃣ PADAM

Proses operasi PADAM pada rekod sebenarnya untuk mencari kedudukan rekod dalam pokok B+ dahulu, dan kemudian mendapatkan kunci rekod X , dan kemudian lakukan operasi tanda padam. Kami juga boleh memahami proses ini seperti menggunakan kaedah baca kunci untuk memperoleh kunci X untuk mencari kedudukan rekod yang akan dipadamkan dalam pepohon B+.

2️⃣ INSERT

Secara amnya, operasi memasukkan rekod baharu tidak dikunci InnoDB melindungi rekod baharu ini melalui jenis kunci tersirat boleh diakses oleh transaksi lain sebelum transaksi ini dilakukan.

3️⃣ KEMASKINI

Terdapat tiga situasi semasa menjalankan operasi KEMASKINI pada rekod:

① Jika nilai kunci rekod belum diubah suai dan adalah Jika ruang storan yang diduduki oleh lajur yang dikemas kini tidak berubah sebelum dan selepas pengubahsuaian, mula-mula cari kedudukan rekod ini dalam pepohon B+, kemudian dapatkan kunci X rekod, dan akhir sekali lakukan operasi pengubahsuaian di kedudukan rekod asal. Kita juga boleh memikirkan proses merekod lokasi yang akan diubah suai dalam pokok B+ sebagai operasi membaca kunci untuk memperoleh kunci X.

② Jika nilai kunci rekod tidak diubah suai dan ruang storan yang diduduki oleh sekurang-kurangnya satu lajur yang dikemas kini telah berubah sebelum dan selepas pengubahsuaian, mula-mula cari kedudukan rekod ini dalam pepohon B+, dan kemudian dapatkan kunci X pada rekod, padam sepenuhnya rekod (iaitu, alihkan rekod sepenuhnya ke senarai sampah), dan akhir sekali masukkan rekod baharu. Proses mencari kedudukan rekod yang akan diubah suai dalam pokok B+ ini dianggap sebagai bacaan terkunci untuk mendapatkan kunci X Rekod yang baru dimasukkan dilindungi oleh kunci tersirat yang disediakan oleh operasi INSERT.

③ Jika nilai kunci rekod diubah suai, ia bersamaan dengan melakukan operasi DELETE pada rekod asal dan kemudian melakukan operasi INSERT Operasi mengunci perlu dilakukan mengikut peraturan DELETE dan INSERT.

PS: Mengapakah transaksi lain masih boleh dibaca apabila kunci tulis dikunci ?

Oleh kerana InnoDB mempunyai mekanisme MVCC (Multiple Version Concurrency Control), bacaan syot kilat boleh digunakan tanpa disekat.

4. Klasifikasi kebutiran kunci

Apakah kebutiran kunci? Butiran kunci yang dipanggil merujuk kepada skop perkara yang anda ingin kunci.

Sebagai contoh, jika anda pergi ke bilik mandi di rumah, anda hanya perlu mengunci bilik mandi anda tidak perlu mengunci seluruh rumah untuk menghalang ahli keluarga daripada masuk.

Apakah butiran penguncian yang munasabah?

Sebenarnya bilik air bukan sahaja digunakan untuk ke tandas, tetapi juga untuk mandi dan mencuci tangan. Ini melibatkan isu mengoptimumkan butiran penguncian.

Apabila anda mandi di bilik air, orang lain sebenarnya boleh masuk dan membasuh tangan mereka pada masa yang sama, asalkan mereka terpencil jika tandas, tab mandi dan singki semuanya berasingan dan agak bebas (basah dan kering dipisahkan, ia adalah ), sebenarnya, bilik mandi boleh digunakan oleh tiga orang pada masa yang sama, sudah tentu, tiga orang itu tidak boleh melakukan perkara yang sama. Ini memperhalusi butiran kunci Anda hanya perlu menutup pintu bilik mandi semasa mandi, dan orang lain masih boleh masuk dan mencuci tangan mereka. Jika kawasan berfungsi yang berbeza tidak dipisahkan semasa mereka bentuk bilik mandi, sumber bilik mandi tidak boleh dimaksimumkan.

Begitu juga, terdapat juga butiran kunci dalam MySQL. Biasanya dibahagikan kepada tiga jenis, kunci baris, kunci meja dan kunci halaman .

4.1 Kunci Baris

Dalam pengenalan kunci kongsi dan kunci eksklusif, ia sebenarnya direkodkan untuk baris tertentu, jadi ia juga boleh dipanggil kunci baris.

Mengunci rekod hanya mempengaruhi rekod ini, jadi butiran penguncian kunci baris adalah yang terbaik dalam MySQL. Kunci lalai enjin storan InnoDB ialah kunci baris.

Ia mempunyai ciri-ciri berikut:

• Kebarangkalian konflik kunci yang paling rendah dan konkurensi yang tinggi

  Disebabkan butiran kunci baris yang kecil, kebarangkalian pertikaian sumber kunci juga adalah yang paling kecil, mengakibatkan konflik kunci Semakin rendah kebarangkalian, semakin tinggi konkurensi.

• Overhed tinggi dan penguncian perlahan

  Kunci sangat memakan prestasi Cuba bayangkan, jika beberapa keping data dalam pangkalan data dikunci, Ia pasti akan menduduki banyak sumber, dan untuk mengunci, anda perlu menunggu kunci sebelumnya dilepaskan sebelum mengunci.

• akan menghasilkan kebuntuan

  Apa itu kebuntuan, anda boleh baca di bawah.

4.2 Kunci meja

Kunci peringkat meja ialah kunci peringkat meja, yang akan mengunci seluruh meja, yang boleh mengelakkan kebuntuan sangat Ia juga merupakan mekanisme penguncian yang paling terperinci dalam MySQL.

Kunci lalai enjin storan MyISAM ialah kunci meja.

Ia mempunyai ciri-ciri berikut:

• Overhead rendah dan mengunci pantas

  Oleh kerana keseluruhan meja dikunci, Kelajuan mestilah lebih pantas daripada mengunci sekeping data.

• Tidak akan ada jalan buntu

  telah mengunci seluruh meja urus niaga lain tidak boleh mendapatkan kunci sama sekali, jadi sudah tentu mereka tidak akan melakukannya Kebuntuan berlaku.

• Kebutiran kunci adalah besar, kebarangkalian konflik kunci adalah tinggi, dan keselarasan adalah rendah

4.3 Kunci halaman

Penguncian peringkat halaman ialah tahap penguncian unik dalam MySQL dan tidak biasa dalam perisian pengurusan pangkalan data yang lain.

Kebutiran kunci peringkat halaman adalah antara kunci peringkat baris dan kunci peringkat meja, jadi overhed sumber yang diperlukan untuk mendapatkan kunci dan keupayaan pemprosesan serentak yang boleh mereka sediakan juga adalah antara dua di atas . Selain itu, kunci peringkat halaman, seperti kunci peringkat baris, boleh menyebabkan kebuntuan.

	行锁	表锁	页锁
锁的粒度	小	大	两者之间
加锁效率	慢	快	两者之间
冲突概率	低	高	-
并发性能	高	低	一般
性能开销	大	小	两者之间
是否死锁	是	否	是

5. 算法实现分类

对于上面的锁的介绍，我们实际上可以知道，主要区分就是在锁的粒度上面，而 InnoDB 中用的锁就是行锁，也叫记录锁，但是要注意，这个记录指的是通过给索引上的索引项加锁。

InnoDB 这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁。

不论是使用主键索引、唯一索引或普通索引，InnoDB 都会使用行锁来对数据加锁。

只有执行计划真正使用了索引，才能使用行锁：即便在条件中使用了索引字段，但是否使用索引来检索数据是由 MySQL 通过判断不同执行计划的代价来决 定的，如果 MySQL 认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下 InnoDB 将使用表锁，而不是行锁。

同时当我们用范围条件而不是相等条件检索数据，并请求锁时，InnoDB 会给符合条件的已有数据记录的索引项加锁。

不过即使是行锁，InnoDB 里也是分成了各种类型的。换言之，即使对同一条记录加上行锁，不同的锁类型也会产生不同的效果。通常有以下几种常用的行锁类型。

5.1 Record Lock

记录锁，单条索引记录上加锁。

Record Lock 锁住的永远是索引，不包括记录本身，即使该表上没有任何索引，那么innodb会在后台创建一个隐藏的聚集主键索引，那么锁住的就是这个隐藏的聚集主键索引。

记录锁是有 S 锁和 X 锁之分的，当一个事务获取了一条记录的 S 型记录锁后，其他事务也可以继续获取该记录的 S 型记录锁，但不可以继续获取 X 型记录锁；当一个事务获取了一条记录的 X 型记录锁后，其他事务既不可以继续获取该记录的 S 型记录锁，也不可以继续获取 X 型记录锁。

5.2 Gap Locks

间隙锁，对索引前后的间隙上锁，不对索引本身上锁。

MySQL 在 REPEATABLE READ 隔离级别下是可以解决幻读问题的，解决方案有两种，可以使用 MVCC 方案解决，也可以采用加锁方案解决。但是在使用加锁方案解决时有问题，就是事务在第一次执行读取操作时，那些幻影记录尚 不存在，我们无法给这些幻影记录加上记录锁。所以我们可以使用间隙锁对其上锁。

如存在这样一张表：

CREATE TABLE test (
    id INT (1) NOT NULL AUTO_INCREMENT,
    number INT (1) NOT NULL COMMENT '数字',
    PRIMARY KEY (id),
    KEY number (number) USING BTREE
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;

# 插入以下数据
INSERT INTO test VALUES (1, 1); 
INSERT INTO test VALUES (5, 3); 
INSERT INTO test VALUES (7, 8); 
INSERT INTO test VALUES (11, 12);

如下：

开启一个事务 A：

BEGIN;

SELECT * FROM test WHERE number = 3 FOR UPDATE;

此时，会对((1,1),(5,3))和((5,3),(7,8))之间上锁。

如果此时在开启一个事务 B 进行插入数据，如下：

BEGIN;

# 阻塞
INSERT INTO test (id, number) VALUES (2,2);

结果如下：

为什么不能插入？因为记录(2,2)要 插入的话，在索引 number上，刚好落在((1,1),(5,3))和((5,3),(7,8))之间，是有锁的，所以不允许插入。 如果在范围外，当然是可以插入的，如：

INSERT INTO test (id, number) VALUES (8,8);

5.3 Next-Key Locks

next-key locks 是索引记录上的记录锁和索引记录之前的间隙上的间隙锁的组合，包括记录本身，每个 next-key locks 是前开后闭区间，也就是说间隙锁只是锁的间隙，没有锁住记录行，next-key locks 就是间隙锁基础上锁住右边界行。

InnoDB 默认使用 REPEATABLE READ 隔离级别。在这种情况下，InnoDB 使用 Next-Key Locks 锁进行搜索和索引扫描，这可以防止幻读的发生。

6. 乐观锁和悲观锁

乐观锁和悲观锁其实不算是具体的锁，而是一种锁的思想，不仅仅是在 MySQL 中体现，常见的 Redis 等中间件都可以应用这种思想。

6.1 乐观锁

所谓乐观锁，就是持有乐观的态度，当我们更新一条记录时，假设这段时间没有其他人来操作这条数据。

实现乐观锁常见的方式

常见的实现方式就是在表中添加 version字段，控制版本号，每次修改数据后+1。

在每次更新数据之前，先查询出该条数据的 version版本号，再执行业务操作，然后在更新数据之前在把查到的版本号和当前数据库中的版本号作对比，若相同，则说明没有其他线程修改过该数据，否则作相应的异常处理。

6.2 悲观锁

所谓悲观锁，就是持有悲观的态度，一开始就假设改数据会被别人修改。

悲观锁的实现方式有两种

共享锁（读锁）和排它锁（写锁），参考上面。

7. 死锁

是指两个或两个以上的进程在执行过程中，由于竞争资源或者由于彼此通信而造成的一种阻塞的现象，若无外力作用，它们都将无法推进下去。此时称系统 处于死锁状态或系统产生了死锁。

产生的条件

• 互斥条件：一个资源每次只能被一个进程使用；

• 请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放；

• 不剥夺条件：进程已获得的资源，在没有使用完之前，不能强行剥夺；

• 循环等待条件：多个进程之间形成的一种互相循环等待的资源的关系。

MySQL 中其实也是一样的，如下还是这样一张表：

CREATE TABLE `user` (
  `id` bigint NOT NULL COMMENT '主键',
  `name` varchar(20) DEFAULT NULL COMMENT '姓名',
  `sex` char(1) DEFAULT NULL COMMENT '性别',
  `age` varchar(10) DEFAULT NULL COMMENT '年龄',
  `url` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `suf_index_url` (`name`(3)) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

# 数据
INSERT INTO `user` (`id`, `name`, `sex`, `age`, `url`) VALUES ('1', 'a', '1', '18', 'https://javatv.net');
INSERT INTO `user` (`id`, `name`, `sex`, `age`, `url`) VALUES ('2', 'b', '1', '18', 'https://javatv.net');

按照如下顺序执行：

	A	B
①	BEGIN
②		BEGIN
③	SELECT * FROM user WHERE name='a' FOR UPDATE
④		SELECT * FROM user WHERE name='b' FOR UPDATE
⑤	SELECT * FROM user WHERE name='b' FOR UPDATE
⑥		SELECT * FROM user WHERE name='a' FOR UPDATE

1、开启 A、B 两个事务；

2、首先 A 先查询name='a'的数据，然后 B 也查询name='b'的数据；

3、在 B 没释放锁的情况下，A 尝试对 name='b'的数据加锁，此时会阻塞；

4、若此时，事务 B 在没释放锁的情况下尝试对 name='a'的数据加锁，则产生死锁。

此时，MySQL 检测到了死锁，并结束了 B 中事务的执行，此时，切回事务 A，发现原本阻塞的 SQL 语句执行完成了。可通过show engine innodb status \G查看死锁。

如何避免

从上面的案例可以看出，死锁的关键在于：两个(或以上)的 Session 加锁的顺序不一致，所以我们在执行 SQL 操作的时候要让加锁顺序一致，尽可能一次性锁定所需的数据行。

Atas ialah kandungan terperinci Apakah kunci dan klasifikasi MySQL?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!