mysql - 為什么innodb下更新A行時(shí)B行也被鎖?。?/h1>
瀏覽：247日期：2022-06-14 18:54:59
問(wèn)題描述
在學(xué)習(xí)MySQL事務(wù)隔離級(jí)別時(shí)，本來(lái)想重現(xiàn)《高性能MySQL》中的死鎖現(xiàn)象（Page 9）。結(jié)果發(fā)現(xiàn)innodb更新單行時(shí)，造成全表被鎖住，不符合innodb行鎖的設(shè)置。
使用的版本：
mysql> status--------------mysql Ver 14.14 Distrib 5.6.26, for Linux (x86_64) using EditLine wrapperConnection id: 2Current database: testCurrent user: root@localhostSSL: Not in useCurrent pager: stdoutUsing outfile: ’’Using delimiter:;Server version: 5.6.26 MySQL Community Server (GPL)Protocol version: 10Connection: Localhost via UNIX socketServer characterset: latin1Db characterset: latin1Client characterset: utf8Conn. characterset: utf8UNIX socket: /var/lib/mysql/mysql.sockUptime: 4 hours 52 min 1 secThreads: 3 Questions: 107 Slow queries: 0 Opens: 69 Flush tables: 1 Open tables: 62 Queries per second avg: 0.006--------------mysql> show variables like ’%isolation%’;+---------------+-----------------+| Variable_name | Value |+---------------+-----------------+| tx_isolation | REPEATABLE-READ |+---------------+-----------------+1 row in set (0.00 sec)
測(cè)試表
mysql> show create table tG;*************************** 1. row *************************** Table: tCreate Table: CREATE TABLE `t` ( `a1` int(11) DEFAULT NULL, `b` varchar(10) DEFAULT NULL, `c` varchar(10) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00 sec)ERROR: No query specifiedmysql> select * from t;+------+------+------+| a1 | b | c |+------+------+------+| 1 | a | b || 2 | aa | bb |+------+------+------+2 rows in set (0.00 sec)
在兩個(gè)獨(dú)立的會(huì)話中創(chuàng)建兩個(gè)事務(wù)
會(huì)話1
mysql> start transaction;Query OK, 0 rows affected (0.00 sec)mysql> update t set b=’x’ where a1=2;Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0
會(huì)話2，在被阻塞一段時(shí)間后會(huì)出現(xiàn)超時(shí)錯(cuò)誤。
mysql> start transaction -> ;Query OK, 0 rows affected (0.00 sec)mysql> update t set c=’yy’ where a1=1;ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
原則上來(lái)說(shuō)，會(huì)話1中的行鎖不會(huì)阻塞會(huì)話2中的更新才對(duì)。
不知道大家是否遇到過(guò)這樣的問(wèn)題，感謝提供相應(yīng)的解答。
問(wèn)題解答
回答1：在a1上添加索引，才是行鎖定。innodb 沒(méi)有索引，照樣是表鎖定
上一條：mysql - INSERT ON DUPLICATE KEY 是如何判斷重復(fù)key的?下一條：mysql - 給千萬(wàn)數(shù)據(jù)的表增加索引，不影響使用，怎么處理？
相關(guān)文章：
1. javascript - immutable配合react提升性能？2. javascript - sublime快鍵鍵問(wèn)題3. javascript - nodejs關(guān)于進(jìn)程間發(fā)送句柄的一點(diǎn)疑問(wèn)4. Apache 已經(jīng)把網(wǎng)站根目錄的改為allow from all了，但是服務(wù)器還是不能訪問(wèn)？5. 實(shí)現(xiàn)bing搜索工具urlAPI提交6. javascript - 移動(dòng)端上不能實(shí)現(xiàn)拖拽布局嗎？7. vue.js - Vue 如何像Angular.js watch 一樣監(jiān)聽(tīng)數(shù)據(jù)變化8. phpstudy8.1支持win11系統(tǒng)嗎？9. 配置Apache時(shí)，添加對(duì)PHP的支持時(shí)語(yǔ)法錯(cuò)誤10. css - 寫(xiě)頁(yè)面遇到個(gè)布局問(wèn)題，求大佬們幫解答，在線等，急！~
問(wèn)題描述
在學(xué)習(xí)MySQL事務(wù)隔離級(jí)別時(shí)，本來(lái)想重現(xiàn)《高性能MySQL》中的死鎖現(xiàn)象（Page 9）。結(jié)果發(fā)現(xiàn)innodb更新單行時(shí)，造成全表被鎖住，不符合innodb行鎖的設(shè)置。
使用的版本：
mysql> status--------------mysql Ver 14.14 Distrib 5.6.26, for Linux (x86_64) using EditLine wrapperConnection id: 2Current database: testCurrent user: root@localhostSSL: Not in useCurrent pager: stdoutUsing outfile: ’’Using delimiter:;Server version: 5.6.26 MySQL Community Server (GPL)Protocol version: 10Connection: Localhost via UNIX socketServer characterset: latin1Db characterset: latin1Client characterset: utf8Conn. characterset: utf8UNIX socket: /var/lib/mysql/mysql.sockUptime: 4 hours 52 min 1 secThreads: 3 Questions: 107 Slow queries: 0 Opens: 69 Flush tables: 1 Open tables: 62 Queries per second avg: 0.006--------------mysql> show variables like ’%isolation%’;+---------------+-----------------+| Variable_name | Value |+---------------+-----------------+| tx_isolation | REPEATABLE-READ |+---------------+-----------------+1 row in set (0.00 sec)
測(cè)試表
mysql> show create table tG;*************************** 1. row *************************** Table: tCreate Table: CREATE TABLE `t` ( `a1` int(11) DEFAULT NULL, `b` varchar(10) DEFAULT NULL, `c` varchar(10) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00 sec)ERROR: No query specifiedmysql> select * from t;+------+------+------+| a1 | b | c |+------+------+------+| 1 | a | b || 2 | aa | bb |+------+------+------+2 rows in set (0.00 sec)
在兩個(gè)獨(dú)立的會(huì)話中創(chuàng)建兩個(gè)事務(wù)
會(huì)話1
mysql> start transaction;Query OK, 0 rows affected (0.00 sec)mysql> update t set b=’x’ where a1=2;Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0
會(huì)話2，在被阻塞一段時(shí)間后會(huì)出現(xiàn)超時(shí)錯(cuò)誤。
mysql> start transaction -> ;Query OK, 0 rows affected (0.00 sec)mysql> update t set c=’yy’ where a1=1;ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
原則上來(lái)說(shuō)，會(huì)話1中的行鎖不會(huì)阻塞會(huì)話2中的更新才對(duì)。
不知道大家是否遇到過(guò)這樣的問(wèn)題，感謝提供相應(yīng)的解答。
問(wèn)題解答
回答1：在a1上添加索引，才是行鎖定。innodb 沒(méi)有索引，照樣是表鎖定
網(wǎng)公網(wǎng)安備