Pemahaman mendalam tentang kunci dalam MySQL (kunci global, kunci peringkat jadual, kunci baris)

Artikel ini akan membawa anda melalui kunci dalam MySQL dan memperkenalkan kunci global, kunci peringkat meja dan kunci baris MySQL. Saya harap ia akan membantu anda!

Mengikut skop penguncian, kunci dalam MySQL boleh dibahagikan secara kasar kepada tiga kategori: kunci global, kunci peringkat meja dan kunci baris

1. Kunci global

Kunci global mengunci keseluruhan contoh pangkalan data. MySQL menyediakan kaedah untuk meningkatkan kunci baca global, arahannya ialah Flush tables with read lock. Apabila anda perlu membuat keseluruhan pustaka dalam keadaan baca sahaja, anda boleh menggunakan arahan ini Selepas itu, penyataan berikut bagi utas lain akan disekat: penyata kemas kini data (tambah, padam dan ubah suai data), penyataan definisi data. (termasuk membuat jadual, mengubah suai struktur jadual, dsb.) dan mengemas kini penyata komitmen transaksi. [Cadangan berkaitan: tutorial mysql(video)]

Senario penggunaan tipikal kunci global ialah membuat sandaran logik pangkalan data penuh. Maksudnya, pilih setiap jadual dalam keseluruhan pangkalan data dan simpannya sebagai teks

Tetapi jika anda membuat keseluruhan pangkalan data baca sahaja, masalah berikut mungkin berlaku:

• Jika anda membuat sandaran pada pangkalan data utama, maka Kemas kini tidak boleh dilakukan semasa tempoh sandaran, dan perniagaan pada dasarnya akan ditutup
• Jika sandaran dilakukan pada pangkalan data hamba, pangkalan data hamba tidak dapat melaksanakan binlog yang disegerakkan daripada pangkalan data induk semasa tempoh sandaran, yang akan menyebabkan kelewatan induk-hamba

Membuka urus niaga di bawah tahap pengasingan bacaan boleh berulang boleh mendapatkan paparan ketekalan

Alat sandaran logik rasmi ialah mysqldump. Apabila mysqldump menggunakan parameter --single-transaction, transaksi akan dimulakan sebelum mengimport data untuk memastikan paparan yang konsisten diperoleh. Disebabkan sokongan MVCC, data boleh dikemas kini seperti biasa semasa proses ini. Transaksi tunggal hanya terpakai kepada perpustakaan yang menggunakan enjin transaksi untuk semua jadual

1 Memandangkan keseluruhan perpustakaan adalah baca sahaja, mengapa tidak menggunakan kaedah set global readonly=true?

• Dalam sesetengah sistem, nilai baca sahaja akan digunakan untuk logik lain, seperti menentukan sama ada perpustakaan ialah perpustakaan utama atau perpustakaan siap sedia. Oleh itu, cara mengubah suai pembolehubah global mempunyai kesan yang lebih besar
• Terdapat perbezaan dalam mekanisme pengendalian pengecualian. Jika pelanggan memutuskan sambungan secara tidak normal selepas melaksanakan jadual Flush dengan arahan kunci baca, MySQL akan secara automatik melepaskan kunci global dan keseluruhan perpustakaan akan kembali ke keadaan di mana ia boleh dikemas kini seperti biasa. Selepas menetapkan keseluruhan perpustakaan kepada baca sahaja, jika pengecualian berlaku pada klien, pangkalan data akan kekal dalam keadaan baca sahaja, yang akan menyebabkan keseluruhan perpustakaan tidak boleh ditulis untuk masa yang lama, dan risikonya tinggi

2. Kunci peringkat jadual

Terdapat dua kunci peringkat jadual dalam MySQL: satu kunci jadual, dan satu lagi kunci data meta (MDL)

Sintaks kunci jadual ialah kunci jadual... baca/tulis. Anda boleh menggunakan jadual buka kunci untuk melepaskan kunci secara aktif, atau anda boleh melepaskannya secara automatik apabila pelanggan memutuskan sambungan. Selain menyekat pembacaan dan penulisan utas lain, sintaks jadual kunci juga mengehadkan objek operasi seterusnya bagi utas ini

Jika pernyataan lock tables t1 read,t2 wirte; dilaksanakan dalam utas A tertentu, utas lain akan menulis t1 , Penyata membaca dan menulis t2 akan disekat. Pada masa yang sama, benang A hanya boleh melakukan operasi membaca t1 dan membaca dan menulis t2 sebelum melaksanakan jadual buka kunci. Malah menulis kepada t1 tidak dibenarkan

Satu lagi jenis kunci aras meja ialah MDL. MDL tidak perlu digunakan secara eksplisit, ia akan ditambah secara automatik apabila mengakses jadual. Fungsi MDL adalah untuk memastikan ketepatan membaca dan menulis. Jika pertanyaan merentasi data dalam jadual, dan utas lain membuat perubahan pada struktur jadual semasa pelaksanaan dan memadamkan lajur, maka hasil yang diperoleh oleh utas pertanyaan tidak akan sepadan dengan struktur jadual, dan ia pasti tidak akan berfungsi

Dalam MySQL versi 5.5, MDL telah diperkenalkan Apabila menambah, memadam, mengubah suai dan menanyakan jadual, tambah kunci baca MDL apabila membuat perubahan struktur pada jadual, tambah kunci tulis MDL

• Kunci baca tidak saling eksklusif, jadi beberapa utas boleh menambah, memadam, mengubah suai dan menyemak jadual pada masa yang sama
• Baca dan tulis kunci , Kunci tulis adalah saling eksklusif dan digunakan untuk memastikan keselamatan operasi yang mengubah struktur jadual. Oleh itu, jika terdapat dua utas yang ingin menambah medan pada jadual pada masa yang sama, salah satu daripadanya perlu menunggu satu lagi selesai melaksanakan sebelum ia boleh mula melaksanakan

Untuk tambah medan pada jadual, atau ubah suai medan, atau tambah Indeks perlu mengimbas data keseluruhan jadual. Apabila mengendalikan meja besar, anda perlu berhati-hati untuk mengelak menjejaskan perkhidmatan dalam talian

sesi A dimulakan dahulu, dan kunci baca MDL akan ditambahkan pada jadual t pada masa ini. Memandangkan sesi B juga memerlukan kunci baca MDL, ia boleh dilaksanakan seperti biasa. Kemudian, sesi C akan disekat kerana kunci baca MDL sesi A belum dikeluarkan, dan sesi C memerlukan kunci tulis MDL, jadi ia hanya boleh disekat. Tidak kira jika hanya sesi C itu sendiri disekat, tetapi semua permintaan masa hadapan untuk memohon kunci baca MDL pada jadual t juga akan disekat oleh sesi C. Semua operasi tambah, padam, ubah suai dan pertanyaan pada jadual perlu memohon kunci baca MDL dahulu, dan kemudian semuanya dikunci, yang bermaksud bahawa jadual itu kini tidak boleh dibaca dan boleh ditulis sepenuhnya

Kunci MDL dalam transaksi digunakan pada permulaan pelaksanaan penyata, tetapi ia tidak akan dikeluarkan serta-merta selepas penyata tamat, tetapi akan dikeluarkan selepas keseluruhan transaksi diserahkan

1. Jika Selamat menambah medan pada jadual kecil?

Pertama sekali, transaksi yang lama mesti diselesaikan Jika transaksi tidak diserahkan, kunci DML akan sentiasa diduduki. Dalam jadual innodb_trx pustaka information_schema MySQL, transaksi yang sedang dilaksanakan boleh ditemui. Jika jadual yang akan ditukar oleh DDL kebetulan mempunyai urus niaga yang lama dilaksanakan, pertimbangkan untuk menjeda DDL dahulu atau mematikan transaksi yang lama

2. Jika jadual yang akan ditukar ialah jadual hotspot, walaupun jumlah data tidak besar, Tetapi permintaan di atas sangat kerap, dan saya perlu menambah medan Apa yang perlu saya lakukan?

Tetapkan masa menunggu dalam penyata jadual ubah Adalah lebih baik jika anda boleh mendapatkan kunci tulis MDL dalam masa menunggu yang ditetapkan Jika anda tidak boleh mendapatkannya, jangan sekat penyata perniagaan berikutnya dan berikan bangun dulu. Kemudian ulangi proses dengan mencuba semula arahan

3. Kunci baris

Kunci baris MySQL dilaksanakan oleh setiap enjin pada lapisan enjin. Tetapi tidak semua enjin menyokong kunci baris Contohnya, enjin MyISAM tidak menyokong kunci baris

Kunci baris ialah kunci untuk rekod baris dalam jadual data. Contohnya, jika transaksi A mengemas kini baris, dan transaksi B juga perlu mengemas kini baris yang sama pada masa ini, kemas kini mesti menunggu sehingga operasi transaksi A selesai

1 protokol kunci

Kunci baris dua rekod yang dipegang oleh transaksi A tidak dikeluarkan sehingga komit Penyata kemas kini transaksi B akan disekat sehingga transaksi A dilaksanakan

Dalam urus niaga InnoDB, kunci baris ditambah apabila diperlukan, tetapi kunci itu tidak dikeluarkan serta-merta apabila ia tidak diperlukan lagi, tetapi dilepaskan sehingga akhir transaksi. Ini ialah protokol kunci dua fasa

Jika berbilang baris perlu dikunci dalam transaksi, kunci yang paling berkemungkinan menyebabkan konflik kunci dan menjejaskan konkurensi harus diletakkan jauh ke belakang mungkin

Andaikan anda ingin melaksanakan transaksi tiket wayang dalam talian dan pelanggan A ingin membeli tiket wayang di pawagam B. Perniagaan perlu melibatkan operasi berikut:

1 Tolak harga tiket wayang daripada baki akaun pelanggan A

2. Tambahkan harga tiket wayang ke baki akaun teater B

3. Rekod log urus niaga

Untuk memastikan atomicity transaksi, ketiga-tiga operasi ini mesti diletakkan dalam satu transaksi. Bagaimana untuk mengatur susunan ketiga-tiga penyata ini dalam urus niaga?

Jika ada pelanggan C lain yang ingin membeli tiket di teater B pada masa yang sama, maka bahagian yang bercanggah antara kedua-dua transaksi ini ialah penyata 2. Kerana mereka ingin mengemas kini baki akaun teater yang sama, mereka perlu mengubah suai baris data yang sama. Menurut protokol penguncian dua fasa, semua kunci baris yang diperlukan untuk operasi dilepaskan apabila transaksi dilakukan. Oleh itu, jika penyata 2 disusun pada penghujung, contohnya dalam susunan 3, 1, 2, maka masa kunci untuk garisan baki akaun teater akan menjadi paling sedikit. Ini meminimumkan kunci menunggu antara urus niaga dan menambah baik keselarasan

2. Kebuntuan dan pengesanan kebuntuan

dalam sistem serentak Kebergantungan sumber pekeliling berlaku dalam urutan yang berbeza terlibat sedang menunggu urutan lain untuk mengeluarkan sumber, utas ini akan memasuki keadaan menunggu yang tidak terhingga, yang dipanggil kebuntuan

Transaksi A sedang menunggu transaksi B untuk melepaskan kunci baris dengan id=2, sementara transaksi B sedang menunggu transaksi A untuk melepaskan kunci baris dengan id=1. Transaksi A dan urus niaga B sedang menunggu sumber masing-masing dikeluarkan, yang bermaksud mereka telah memasuki keadaan buntu. Apabila kebuntuan berlaku, terdapat dua strategi:

• Satu strategi ialah menunggu terus sehingga tamat masa. Tamat masa ini boleh ditetapkan melalui parameter innodb_lock_wait_timeout
• Strategi lain adalah untuk memulakan pengesanan jalan buntu Selepas kebuntuan ditemui, gulung semula transaksi dalam rantaian kebuntuan secara proaktif untuk membolehkan transaksi lain terus dilaksanakan. Tetapkan parameter innodb_deadlock_detect kepada hidup, yang bermaksud menghidupkan logik ini

Dalam InnoDB, nilai lalai innodb_lock_wait_timeout ialah 50s, yang bermaksud bahawa jika strategi pertama diguna pakai, apabila kebuntuan berlaku, yang pertama satu ialah Benang yang dikunci akan tamat masa dan keluar selepas 50 saat, dan kemudian benang lain mungkin terus dilaksanakan. Untuk perkhidmatan dalam talian, masa menunggu ini selalunya tidak boleh diterima

Dalam keadaan biasa, strategi semakan jalan buntu aktif mesti diguna pakai dan nilai lalai innodb_deadlock_detect itu sendiri dihidupkan. Pemantauan kebuntuan aktif boleh mengesan dan mengendalikan kebuntuan dengan cepat apabila ia berlaku, tetapi ia mempunyai beban tambahan. Setiap kali transaksi dikunci, adalah perlu untuk menyemak sama ada benang yang bergantung padanya dikunci oleh orang lain, dan seterusnya, dan akhirnya menentukan sama ada terdapat menunggu bulat, iaitu kebuntuan

Jika semua transaksi adalah Untuk mengemas kini senario baris yang sama, setiap utas yang baru disekat mesti menentukan sama ada penambahannya sendiri akan menyebabkan kebuntuan Ini adalah operasi dengan kerumitan masa O(n)

Bagaimana untuk menyelesaikan masalah prestasi yang disebabkan oleh. kemas kini baris yang hangat?

1 Jika anda memastikan perniagaan ini tidak akan menemui jalan buntu, anda boleh mematikan pengesanan kebuntuan buat sementara waktu

2

3. Tukar satu baris kepada berbilang baris yang logik untuk mengurangkan konflik kunci. Mengambil akaun teater sebagai contoh, anda boleh mempertimbangkan untuk meletakkannya pada berbilang rekod, seperti 10 rekod Jumlah nilai akaun teater adalah sama dengan jumlah nilai 10 rekod ini. Dengan cara ini, setiap kali anda ingin menambah wang pada akaun teater, anda boleh memilih secara rawak satu daripada rekod untuk ditambahkan. Dengan cara ini, kebarangkalian setiap konflik menjadi 1/10 daripada ahli asal, yang boleh mengurangkan bilangan menunggu kunci dan mengurangkan penggunaan CPU pengesanan kebuntuan

4. Mengapa saya hanya menyemak satu baris kenyataan?

Bina jadual dengan dua medan id dan c, dan masukkan 100,000 baris rekod ke dalamnya

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE DEFINER=`root`@`%` PROCEDURE `idata`()
BEGIN
	declare i int;
  set i=1;
  while(i<=100000) do
    insert into t values(i,i);
    set i=i+1;
  end while;
END

1: Pertanyaan tidak dikembalikan lama

select * from t3 where id=1;

Hasil pertanyaan tidak kembali untuk masa yang lama Gunakan arahan senarai proses untuk menyemak status penyata semasa

1) , tunggu untuk kunci MDL

Seperti yang ditunjukkan dalam rajah di bawah, gunakan perintah show processlist; untuk melihat gambar rajah Menunggu kunci metadata jadual

Status ini menunjukkan bahawa pada masa ini terdapat urutan Kunci tulis MDL diminta atau dipegang pada jadual t, menyekat pernyataan pilihan

Berulang senario:

sessionA lulus Perintah jadual kunci memegang kunci tulis MDL pada jadual t, dan pertanyaan pada sesi B perlu memperoleh kunci baca MDL. Oleh itu, cara untuk menangani masalah jenis ini apabila sessionB memasuki keadaan menunggu

adalah untuk mengetahui siapa yang memegang kunci tulis MDL dan kemudian membunuhnya. Walau bagaimanapun, dalam hasil senarai proses tunjukkan, lajur Perintah sessionA ialah Tidur, yang menjadikannya menyusahkan untuk mencari Anda boleh mengetahui secara langsung id proses yang menyebabkan penyekatan dengan menanyakan jadual sys.schema_table_lock_waits dan putuskan sambungan dengan bunuh. arahan ( Anda perlu menetapkan performance_schema=on apabila memulakan MySQL. Berbanding dengan menetapkannya kepada off, akan terdapat kira-kira 10% kehilangan prestasi)

select blocking_pid from sys.schema_table_lock_waits;

2), tunggu flush

Laksanakan pernyataan SQL berikut pada jadual t:

select * from information_schema.processlist where id=1;

Didapati bahawa status benang sedang Menunggu siram jadual

Status ini bermaksud bahawa pada masa ini terdapat perambut benang Lakukan operasi siram di atas meja t. Secara amnya terdapat dua penggunaan operasi flush pada jadual dalam MySQL:

flush tables t with read lock;flush tables with read lock;

Dua pernyataan flush ini, jika jadual t dinyatakan, bermakna hanya jadual t ditutup jika tiada nama jadual tertentu, bermakna menutup semua buka jadual dalam MySQL

Tetapi dalam keadaan biasa, kedua-dua penyataan ini dilaksanakan dengan sangat cepat, melainkan ia disekat oleh utas lain

Jadi, di sana Kemungkinan situasi Menunggu status flush meja ialah: a perintah flush tables disekat oleh pernyataan lain, dan kemudian ia menyekat pernyataan pilih

Senario berulang:

Dalam sessionA, sleep(1) dipanggil sekali untuk setiap baris, jadi kenyataan ini akan dilaksanakan selama 100,000 saat secara lalai Dalam tempoh ini, jadual t telah dibuka oleh sesiA. Kemudian, apabila sessionB mengepam jadual t dan kemudian menutup jadual t, ia perlu menunggu pertanyaan sessionA tamat. Dengan cara ini, jika sessionC ingin bertanya lagi, ia akan disekat oleh arahan flush

3), kunci baris menunggu

select * from t where id=1 lock in share mode;

kerana akses Kunci baca diperlukan untuk rekod dengan id=1 Jika sudah ada transaksi yang memegang kunci tulis pada rekod ini pada masa ini, pernyataan pilih akan disekat

Senario berulang:

sessionA memulakan transaksi, memegang kunci tulis dan belum menyerahkannya lagi, itulah sebab mengapa sessionB disekat

Selepas sessionB melaksanakan 1 juta kenyataan kemas kini, 1 juta log balik dijana

带lock in share mode的语句是当前读，因此会直接读到1000001这个结果，速度很快；而select * from t where id=1这个语句是一致性读，因此需要从1000001开始，依次执行undo log，执行了100万次以后，才将1这个结果返回

五、间隙锁

建表和初始化语句如下：

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

这个表除了主键id外，还有一个索引c

为了解决幻读问题，InnoDB引入了间隙锁，锁的就是两个值之间的空隙

当执行select * from t where d=5 for update的时候，就不止是给数据库中已有的6个记录加上了行锁，还同时加了7个间隙锁。这样就确保了无法再插入新的记录

行锁分成读锁和写锁

跟间隙锁存在冲突关系的是往这个间隙中插入一个记录这个操作。间隙锁之间不存在冲突关系

这里sessionB并不会被堵住。因为表t里面并没有c=7会这个记录，因此sessionA加的是间隙锁(5,10)。而sessionB也是在这个间隙加的间隙锁。它们用共同的目标，保护这个间隙，不允许插入值。但它们之间是不冲突的

间隙锁和行锁合称next-key lock，每个next-key lock是前开后闭区间。表t初始化以后，如果用select * from t for update要把整个表所有记录锁起来，就形成了7个next-key lock，分别是(-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +supremum]。因为+∞是开区间，在实现上，InnoDB给每个索引加了一个不存在的最大值supremum，这样才符合都是前开后闭区间

间隙锁和next-key lock的引入，解决了幻读的问题，但同时也带来了一些困扰

间隙锁导致的死锁：

1.sessionA执行select … for update语句，由于id=9这一行并不存在，因此会加上间隙锁(5,10)

2.sessionB执行select … for update语句，同样会加上间隙锁(5,10)，间隙锁之间不会冲突

3.sessionB试图插入一行(9,9,9)，被sessionA的间隙锁挡住了，只好进入等待

4.sessionA试图插入一行(9,9,9)，被sessionB的间隙锁挡住了

两个session进入互相等待状态，形成了死锁

间隙锁的引入可能会导致同样的语句锁住更大的范围，这其实是影响并发度的

在读提交隔离级别下，不存在间隙锁

六、next-key lock

表t的建表语句和初始化语句如下：

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

1、next-key lock加锁规则

• 原则1：加锁的基本单位是next-key lock，next-key lock是前开后闭区间
• 原则2：查找过程中访问到的对象才会加锁
• 优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁
• 优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁
• 一个bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止

这个规则只限于MySQL5.x系列

2、案例一：等值查询间隙锁

1.由于表t中没有id=7的记录，根据原则1，加锁单位是next-key lock，sessionA加锁范围就是(5,10]

2.根据优化2，这是一个等值查询(id=7)，而id=10不满足查询条件，next-key lock退化成间隙锁，因此最终加锁的范围是(5,10)

所以，sessionB要往这个间隙里面插入id=8的记录会被锁住，但是sessionC修改id=10这行是可以的

3、案例二：非唯一索引等值锁

1.根据原则1，加锁单位是next-key lock，因此会给(0,5]加上next-key lock

2.c是普通索引，因此访问c=5这一条记录是不能马上停下来的，需要向右遍历，查到c=10才放弃。根据原则2，访问到的都要加锁，因此要给(5,10]加next-key lock

3.根据优化2，等值判断，向右遍历，最后一个值不满足c=5这个等值条件，因此退化成间隙锁(5,10)

4.根据原则2，只有访问到的对象才会加锁，这个查询使用覆盖索引，并不需要访问主键索引，所以主键索引上没有任何锁，这就是为什么sessionB的update语句可以执行完成

锁是加在索引上的，在这个例子中，lock in share mode只锁覆盖索引，但是如果是for update，系统会认为你接下来要更新数据，因此会顺便给主键索引上满足条件的行加上行锁，这样的话sessionB的update语句会被阻塞住。如果你要用 lock in share mode 来给行加读锁避免数据被更新的话，就必须得绕过覆盖索引的优化，在查询字段中加入索引中不存在的字段

4、案例三：主键索引范围锁

1.开始执行的时候，要找到第一个id=10的行，因此本该是next-key lock(5,10]。根据优化1，主键id上的等值条件，退化成行锁，只加了id=10这一行的行锁

2.范围查询就往后继续找，找到id=15这一行停下来，因此需要加next-key lock(10,15]

所以，sessionA这时候锁的范围就是主键索引上，行锁id=10和next-key lock(10,15]

5、案例四：非唯一索引范围锁

这次sessionA用字段c来判断，加锁规则跟案例三唯一的不同是：在第一次用c=10定位记录的时候，索引c上加上(5,10]这个next-key lock后，由于索引c是非唯一索引，没有优化规则，因此最终sessionA加的锁是索引c上的(5,10]和(10,15]这两个next-key lock

6、案例五：唯一索引范围锁bug

sessionA是一个范围查询，按照原则1的话，应该是索引id上只加(10,15]这个next-key lock，并且因为id是唯一键，所以循环判断到id=15这一行就应该停止了

但是实现上，InnoDB会扫描到第一个不满足条件的行为止，也就是id=20。而且由于这是个范围扫描，因此索引id上的(15,20]这个next-key lock也会被锁上

所以，sessionB要更新id=20这一行是会被锁住的。同样地，sessionC要插入id=16的一行，也会被锁住

7、案例六：非唯一索引上存在等值的例子

insert into t values(30,10,30);

新插入的这一行c=10，现在表里有两个c=10的行。虽然有两个c=10，但是它们的主键值id是不同的，因此这两个c=10的记录之间也是有间隙的

sessionA在遍历的时候，先访问第一个c=10的记录。根据原则1，这里加的是(c=5,id=5)到(c=10,id=10)这个next-key lock。然后sessionA向右查找，直到碰到(c=15,id=15)这一行，循环才结束。根据优化2，这是一个等值查询，向右查找到了不满足条件的行，所以会退化成(c=10,id=10)到(c=15,id=15)的间隙锁

也就是说，这个delete语句在索引c上的加锁范围，就是下图中蓝色区域覆盖的部分，这个蓝色区域左右两边都是虚线，表示开区间

8、案例七：limit语句加锁

加了limit 2的限制，因此在遍历到(c=10,id=30)这一行之后，满足条件的语句已经有两条，循环就结束了。因此，索引c上的加锁范围就变成了从(c=5,id=5)到(c=10,id=30)这个前开后闭区间，如下图所示：

再删除数据的时候尽量加limit，这样不仅可以控制删除数据的条数，让操作更安全，还可以减小加锁的范围

9、案例八：一个死锁的例子

1.sessionA启动事务后执行查询语句加lock in share mode，在索引c上加了next-key lock(5,10]和间隙锁(10,15)

2.sessionB的update语句也要在索引c上加next-key lock(5,10]，进入锁等待

3.然后sessionA要再插入(8,8,8)这一行，被sessionB的间隙锁锁住。由于出现了死锁，InnoDB让sessionB回滚

sessionB的加next-key lock(5,10]操作，实际上分成了两步，先是加(5,10)间隙锁，加锁成功；然后加c=10的行锁，这时候才被锁住的

七、用动态的观点看加锁

表t的建表语句和初始化语句如下：

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

1、不等号条件里的等值查询

begin;
select * from t where id>9 and id<12 order by id desc for update;

利用上面的加锁规则，这个语句的加锁范围是主键索引上的(0,5]、(5,10]和(10,15)。加锁单位是next-key lock，这里用到了优化2，即索引上的等值查询，向右遍历的时候id=15不满足条件，所以next-key lock退化为了间隙锁(10,15)

1.首先这个查询语句的语义是order by id desc，要拿到满足条件的所有行，优化器必须先找到第一个id

2.这个过程是通过索引树的搜索过程得到的，在引擎内部，其实是要找到id=12的这个值，只是最终没找到，但找到了(10,15)这个间隙

3.然后根据order by id desc，再向左遍历，在遍历过程中，就不是等值查询了，会扫描到id=5这一行，所以会加一个next-key lock (0,5]

在执行过程中，通过树搜索的方式定位记录的时候，用的是等值查询的方法

2、等值查询的过程

begin;
select id from t where c in(5,20,10) lock in share mode;

这条in语句使用了索引c并且rows=3，说明这三个值都是通过B+树搜索定位的

在查找c=5的时候，先锁住了(0,5]。但是因为c不是唯一索引，为了确认还有没有别的记录c=5，就要向右遍历，找到c=10确认没有了，这个过程满足优化2，所以加了间隙锁(5,10)。执行c=10会这个逻辑的时候，加锁的范围是(5,10]和(10,15)，执行c=20这个逻辑的时候，加锁的范围是(15,20]和(20,25)

这条语句在索引c上加的三个记录锁的顺序是：先加c=5的记录锁，再加c=10的记录锁，最后加c=20的记录锁

select id from t where c in(5,20,10) order by c desc for update;

由于语句里面是order by c desc，这三个记录锁的加锁顺序是先锁c=20，然后c=10，最后是c=5。这两条语句要加锁相同的资源，但是加锁顺序相反。当这两条语句并发执行的时候，就可能出现死锁

八、insert语句的锁为什么这么多？

1、insert … select语句

表t和t2的表结构、初始化数据语句如下：

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(null, 1,1);
insert into t values(null, 2,2);
insert into t values(null, 3,3);
insert into t values(null, 4,4);

create table t2 like t;

在可重复读隔离级别下，binlog_format=statement时执行下面这个语句时，需要对表t的所有行和间隙加锁

insert into t2(c,d) select c,d from t;

2、insert循环写入

要往表t2中插入一行数据，这一行的c值是表t中c值的最大值加1，SQL语句如下：

insert into t2(c,d)  (select c+1, d from t force index(c) order by c desc limit 1);

这个语句的加锁范围，就是表t索引c上的(3,4]和(4,supermum]这两个next-key lock，以及主键索引上id=4这一行

执行流程是从表t中按照索引c倒序吗，扫描第一行，拿到结果写入到表t2中，因此整条语句的扫描行数是1

但如果要把这一行的数据插入到表t中的话：

insert into t(c,d)  (select c+1, d from t force index(c) order by c desc limit 1);

explain结果中的Extra字段中Using temporary字段，表示这个语句用到了临时表

执行流程如下：

1.创建临时表，表里有两个字段c和d

2.按照索引c扫描表t，依次取c=4、3、2、1，然后回表，读到c和d的值写入临时表

3.由于语义里面有limit 1，所以只取了临时表的第一行，再插入到表t中

这个语句会导致在表t上做全表扫描，并且会给索引c上的所有间隙都加上共享的next-key lock。所以，这个语句执行期间，其他事务不能在这个表上插入数据

需要临时表是因为这类一边遍历数据，一边更新数据的情况，如果读出来的数据直接写回原表，就可能在遍历过程中，读到刚刚插入的记录，新插入的记录如果参与计算逻辑，就跟语义不符

3、insert唯一键冲突

sessionA执行的insert语句，发生唯一键冲突的时候，并不只是简单地报错返回，还在冲突的索引上加了锁，sessionA持有索引c上的(5,10]共享next-key lock（读锁）

在sessionA执行rollback语句回滚的时候，sessionC几乎同时发现死锁并返回

1.在T1时刻，启动sessionA，并执行insert语句，此时在索引c的c=5上加了记录锁。这个索引是唯一索引，因此退化为记录锁

2.在T2时刻，sessionA回滚。这时候，sessionB和sessionC都试图继续执行插入操作，都要加上写锁。两个session都要等待对方的行锁，所以就出现了死锁

4、insert into … on duplicate key update

上面这个例子是主键冲突后直接报错，如果改写成

insert into t values(11,10,10) on duplicate key update d=100;

就会给索引c上(5,10]加一个排他的next-key lock（写锁）

insert into … on duplicate key update的语义逻辑是，插入一行数据，如果碰到唯一键约束，就继续执行后面的更新语句。如果有多个列违反了唯一性索引，就会按照索引的顺序，修改跟第一个索引冲突的行

表t里面已经有了(1,1,1)和(2,2,2)这两行，执行这个语句效果如下：

主键id是先判断的，MySQL认为这个语句跟id=2这一行冲突，所以修改的是id=2的行

思考题：

1、如果要删除一个表里面的前10000行数据，有以下三种方法可以做到：

• 第一种，直接执行delete from T limit 10000;
• 第二种，在一个连接中循环执行20次delete from T limit 500；
• 第三种，在20个连接中同时执行delete from T limit 500；

选择哪一种方式比较好？

参考答案：

第一种方式，单个语句占用时间长，锁的时间也比较长，而且大事务还会导致主从延迟

第三种方式，会人为造成锁冲突

第二种方式相对较好

更多编程相关知识，请访问：编程入门！！

Atas ialah kandungan terperinci Pemahaman mendalam tentang kunci dalam MySQL (kunci global, kunci peringkat jadual, kunci baris). Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!