Mari kita bincangkan tentang kebuntuan yang disebabkan oleh dua pernyataan INSERT

Artikel ini membawakan anda isu-isu berkaitan tentang kebuntuan dalam mysql Ia terutamanya memperkenalkan pengetahuan yang berkaitan tentang kebuntuan yang disebabkan oleh dua kenyataan INSERT yang serupa.

Dua kenyataan INSERT yang sama sebenarnya menyebabkan jalan buntu Adakah ini penyelewengan sifat manusia atau kehilangan moral Kita tidak boleh mengeluh? Ini juga boleh menyebabkan kebuntuan, dan kemudian kod perniagaan ditukar tanpa daya dengan air mata.

Baiklah, sebelum kita menganalisis secara mendalam mengapa dua pernyataan INSERT yang serupa boleh menyebabkan kebuntuan, mari kita perkenalkan beberapa pengetahuan asas dahulu.

Sediakan persekitaran

Untuk kelancaran perkembangan cerita, kami mencipta jadual wira baharu yang telah digunakan berkali-kali:

CREATE TABLE hero (
    number INT AUTO_INCREMENT,
    name VARCHAR(100),
    country varchar(100),
    PRIMARY KEY (number),
    UNIQUE KEY uk_name (name)
) Engine=InnoDB CHARSET=utf8;

Kemudian masukkan beberapa rekod ke dalam jadual ini :

INSERT INTO hero VALUES
    (1, 'l刘备', '蜀'),
    (3, 'z诸葛亮', '蜀'),
    (8, 'c曹操', '魏'),
    (15, 'x荀彧', '魏'),
    (20, 's孙权', '吴');

Kini jadual wira mempunyai dua indeks (indeks sekunder yang unik dan indeks berkelompok Gambar rajah adalah seperti berikut:

<). 🎜 >Cara mengunci pernyataan INSERT

Rakan yang telah membaca "Cara MySQL Runs: Understanding MySQL from the Fundamentals" mesti tahu bahawa pernyataan INSERT tidak akan menghasilkan struktur kunci semasa pelaksanaan biasa Ia adalah The trx_id hidden lajur yang disertakan dengan rekod indeks berkelompok digunakan sebagai kunci tersirat untuk melindungi rekod.

Walau bagaimanapun, dalam beberapa senario khas, penyataan INSERT masih akan menjana struktur kunci:

1 Rekod seterusnya yang akan dimasukkan telah pun kunci jurang ditambahkan pada transaksi lain

Setiap kali rekod baharu dimasukkan, anda perlu menyemak sama ada rekod seterusnya yang akan dimasukkan telah ditambahkan pada kunci jurang jika kunci jurang telah ditambah, , pernyataan INSERT harus disekat dan kunci niat sisip dijana.

Sebagai contoh, untuk jadual wira, transaksi T1 berjalan dalam tahap pengasingan BACA BERULANG (dirujuk sebagai RR pada masa hadapan, dan READ COMMITTED juga akan dirujuk sebagai RC pada masa hadapan) tahap pengasingan dan melaksanakan perkara berikut pernyataan:

# 事务T1
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM hero WHERE number < 8 FOR UPDATE;
+--------+------------+---------+
| number | name       | country |
+--------+------------+---------+
|      1 | l刘备      | 蜀      |
|      3 | z诸葛亮    | 蜀      |
+--------+------------+---------+
2 rows in set (0.02 sec)

Pernyataan ini akan menambah kunci kekunci seterusnya jenis X pada tiga rekod dengan nilai kunci utama 1, 3 dan 8. Jika anda tidak percaya, mari gunakan Tunjukkan kenyataan STATUS INNODB ENJIN untuk melihat keadaan penguncian, seperti yang ditunjukkan dalam gambar Rekod yang ditunjukkan oleh anak panah ialah rekod dengan nilai nombor 8:

Petua:

Mengenai cara mengunci penyataan PILIH, PADAM, dan KEMASKINI , kami telah menganalisisnya dalam artikel sebelum ini dan tidak akan menerangkan secara terperinci di sini.

Pada masa ini, transaksi T2 ingin memasukkan rekod indeks berkelompok dengan nilai kunci utama 4. Sebelum T2 memasukkan rekod, ia mesti mencari rekod indeks berkelompok dengan nilai kunci utama sebanyak 4 pada halaman dalam kedudukan, didapati bahawa nilai kunci utama rekod seterusnya dengan nilai kunci utama 4 ialah 8, dan rekod indeks berkelompok dengan nilai kunci utama 8 telah ditambah dengan kunci jurang. (kunci kekunci seterusnya termasuk kunci rekod yang serius dan kunci jurang), maka transaksi 1 perlu memasuki keadaan menyekat dan menjana struktur kunci jenis memasukkan kunci niat.

Mari kita laksanakan penyataan INSERT dalam transaksi T2 untuk mengesahkan:

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO hero VALUES(4, 'g关羽', '蜀');

Pada masa ini, T2 memasuki keadaan menyekat Mari kita gunakan STATUS SHOW ENGINE INNODB untuk menyemak keadaan penguncian:

Ia boleh dilihat bahawa T2 menambah kunci niat sisipan pada rekod indeks berkelompok dengan nilai kunci utama 8 (iaitu, lock_mode X mengunci jurang sebelum niat sisipan semula ditunjuk dengan anak panah), dan dalam keadaan menunggu.

Baiklah, selepas pengesahan, mari kita lihat bagaimana ia dilaksanakan dalam kod:

Fungsi lock_rec_insert_check_and_lock digunakan untuk melihat sama ada transaksi lain disekat. Untuk INSERT ini, jika ya, maka transaksi ini akan menghasilkan kunci niat sisipan untuk rekod yang telah ditambah dengan kunci jurang oleh transaksi lain Proses khusus adalah seperti berikut:

Petua:

Fungsi lock_rec_other_has_conflicting digunakan untuk mengesan sama ada kunci yang akan diperolehi kali ini bercanggah dengan kunci sedia ada pada rekod.

2 Apabila kunci pendua ditemui

Jika semasa memasukkan rekod baharu, kunci utama rekod sedia ada pada halaman ditemui Atau lajur indeks sekunder unik mempunyai nilai yang sama dengan kunci utama atau lajur indeks sekunder unik rekod yang akan dimasukkan (tetapi nilai lajur indeks sekunder unik bagi berbilang rekod boleh NULL pada masa yang sama, ini situasi tidak dipertimbangkan di sini), pada masa ini transaksi yang memasukkan rekod baharu akan memperoleh kunci rekod dengan nilai kunci yang sama yang telah wujud dalam halaman.

Jika nilai kunci primer diduplikasi, maka:

• Apabila tahap pengasingan tidak lebih besar daripada RC, transaksi yang memasukkan rekod baharu akan menduplikasikan primer sedia ada nilai kunci. Tambahkan kunci rekod formal jenis S pada rekod indeks berkelompok.

• Apabila tahap pengasingan tidak kurang daripada RR, transaksi yang memasukkan rekod baharu akan menambah kunci kekunci seterusnya jenis S pada rekod indeks berkelompok sedia ada dengan kunci primer pendua nilai.

如果是唯一二级索引列重复，那不论是哪个隔离级别，插入新记录的事务都会给已存在的二级索引列值重复的二级索引记录添加S型next-key锁，再强调一遍，加的是next-key锁！加的是next-key锁！加的是next-key锁！这是rc隔离级别中为数不多的给记录添加gap锁的场景。

小贴士：

本来设计InnoDB的大叔并不想在RC隔离级别引入gap锁，但是由于某些原因，如果不添加gap锁的话，会让唯一二级索引中出现多条唯一二级索引列值相同的记录，这就违背了UNIQUE约束。所以后来设计InnoDB的大叔就很不情愿的在RC隔离级别也引入了gap锁。

我们也来做一个实验，现在假设上边的T1和T2都回滚了，现在将隔离级别调至RC，重新开启事务进行测试。

mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.01 sec)
# 事务T1
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO hero VALUES(30, 'x荀彧', '魏');
ERROR 1062 (23000): Duplicate entry 'x荀彧' for key 'uk_name'

然后执行SHOW ENGINE INNODB STATUS语句看一下T1加了什么锁：

可以看到即使现在T1的隔离级别为RC，T1仍然给name列值为'x荀彧'的二级索引记录添加了S型next-key锁（图中红框中的lock mode S）。

如果我们的INSERT语句还带有ON DUPLICATE KEY... 这样的子句，如果遇到主键值或者唯一二级索引列值重复的情况，会对B+树中已存在的相同键值的记录加X型锁，而不是S型锁（不过具体锁的具体类型是和前面描述一样的）。

好了，又到了看代码求证时间了，我们看一下吧：

row_ins_scan_sec_index_for_duplicate是检测唯一二级索引列值是否重复的函数，具体加锁的代码如下所示：

如上图所示，在遇到唯一二级索引列重复的情况时：

• 1号红框表示对带有ON DUPLICATE ...子句时的处理方案，具体就是添加X型锁。

• 2号红框表示对正常INSERT语句的处理方案，具体就是添加S型锁。

不过不论是那种情况，添加的lock_typed的值都是LOCK_ORDINARY，表示next-key锁。

在主键重复时INSERT语句的加锁代码我们就不列举了。

3. 外键检查时

当我们向子表中插入记录时，我们分两种情况讨论：

• 当子表中的外键值可以在父表中找到时，那么无论当前事务是什么隔离级别，只需要给父表中对应的记录添加一个S型正经记录锁就好了。

• 当子表中的外键值在父表中找不到时：那么如果当前隔离级别不大于RC时，不对父表记录加锁；当隔离级别不小于RR时，对父表中该外键值所在位置的下一条记录添加gap锁。

死锁要出场了

好了，基础知识预习完了，该死锁出场了。

看下边这个平平无奇的INSERT语句：

INSERT INTO hero(name, country) VALUES('g关羽', '蜀'), ('d邓艾', '魏');

这个语句用来插入两条记录，不论是在RC，还是RR隔离级别，如果两个事务并发执行它们是有一定几率触发死锁的。为了稳定复现这个死锁，我们把上边一条语句拆分成两条语句：

INSERT INTO hero(name, country) VALUES('g关羽', '蜀');
INSERT INTO hero(name, country) VALUES('d邓艾', '魏');

拆分前和拆分后起到的作用是相同的，只不过拆分后我们可以人为的控制插入记录的时机。如果T1和T2的执行顺序是这样的：

也就是：

• T1先插入name值为g关羽的记录，可以插入成功，此时对应的唯一二级索引记录被隐式锁保护，我们执行SHOW ENGINE INNODB STATUS语句，发现啥一个行锁（row lock）都没有（因为SHOW ENGINE INNODB STATUS不显示隐式锁）：

• 接着T2也插入name值为g关羽的记录。由于T1已经插入name值为g关羽的记录，所以T2在插入二级索引记录时会遇到重复的唯一二级索引列值，此时T2想获取一个S型next-key锁，但是T1并未提交，T1插入的name值为g关羽的记录上的隐式锁相当于一个X型正经记录锁（RC隔离级别），所以T2向获取S型next-key锁时会遇到锁冲突，T2进入阻塞状态，并且将T1的隐式锁转换为显式锁（就是帮助T1生成一个正经记录锁的锁结构）。这时我们再执行SHOW ENGINE INNODB STATUS语句：

可见，T1持有的name值为g关羽的隐式锁已经被转换为显式锁（X型正经记录锁，lock_mode X locks rec but not gap）；T2正在等待获取一个S型next-key锁（lock mode S waiting）。

• 接着T1再插入一条name值为d邓艾的记录。在插入一条记录时，会在页面中先定位到这条记录的位置。在插入name值为d邓艾的二级索引记录时，发现现在页面中的记录分布情况如下所示：

很显然，name值为'd邓艾'的二级索引记录所在位置的下一条二级索引记录的name值应该是'g关羽'（按照汉语拼音排序）。那么在T1插入name值为d邓艾的二级索引记录时，就需要看一下name值为'g关羽'的二级索引记录上有没有被别的事务加gap锁。

有同学想说：目前只有T2想在name值为'g关羽'的二级索引记录上添加S型next-key锁（next-key锁包含gap锁），但是T2并没有获取到锁呀，目前正在等待状态。那么T1不是能顺利插入name值为'g关羽'的二级索引记录么？

我们看一下执行结果：

# 事务T2
mysql> INSERT INTO hero(name, country) VALUES('g关羽', '蜀');
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

很显然，触发了一个死锁，T2被InnoDB回滚了。

这是为啥呢？T2明明没有获取到name值为'g关羽'的二级索引记录上的S型next-key锁，为啥T1还不能插入入name值为d邓艾的二级索引记录呢？

这我们还得回到代码上来，看一下插入新记录时是如何判断锁是否冲突的：

看一下画红框的注释，意思是：只要别的事务生成了一个显式的gap锁的锁结构，不论那个事务已经获取到了该锁(granted)，还是正在等待获取（waiting），当前事务的INSERT操作都应该被阻塞。

回到我们的例子中来，就是T2已经在name值为'g关羽'的二级索引记录上生成了一个S型next-key锁的锁结构，虽然T2正在阻塞（尚未获取锁），但是T1仍然不能插入name值为d邓艾的二级索引记录。

这样也就解释了死锁产生的原因：

• T1在等待T2释放name值为'g关羽'的二级索引记录上的gap锁。

• T2在等待T1释放name值为'g关羽'的二级索引记录上的X型正经记录锁。

两个事务相互等待对方释放锁，这样死锁也就产生了。

怎么解决这个死锁问题？

两个方案：

• 方案一：一个事务中只插入一条记录。

• 方案二：先插入name值为'd邓艾'的记录，再插入name值为'g关羽'的记录

推荐学习：mysql视频教程

Atas ialah kandungan terperinci Mari kita bincangkan tentang kebuntuan yang disebabkan oleh dua pernyataan INSERT. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!