MySQL分散式悲觀鎖原理：
條件
FOR UPDATE 僅適用於InnoDB儲存引擎，且必須在事務區塊(BEGIN/COMMIT)中才能生效。
mysql預設情況下每個sql都是單獨的一個事務，並且是自動提交事務。
測試之前需要設定成非自動提交事務，不然無法模擬併發訪問:
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+
1 row in set (0.00 sec)
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 0 |
+--------------+
1 row in set (0.00 sec)此修改只針對當前視窗有效，重新開啟的新視窗依然是自動提交事務的
所以要就需要兩個視窗，視窗a：非自動提交事務，用於for update操作；
視窗b：用於普通update操作。
測試
我們有一資料庫 test1，有一張表testa ，有自增主鍵ID，name，id_card
表中有兩條資料
mysql> select * from testa;
+----+-------+--------------------+
| id | name | id_card |
+----+-------+--------------------+
| 1 | wangb | 322343256564545754 |
| 2 | shuna | 320990348823998792 |
+----+-------+--------------------+
2 rows in set (0.00 sec)
mysql> desc testa;
+---------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
| id_card | varchar(18) | YES | UNI | NULL | |
+---------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
1.只明確主鍵
- 有資料
在a視窗進行開啟事務，對id為1的資料進行 for update，此時並沒有commit；
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from testa where id = 1 for update;
+----+------+--------------------+
| id | name | id_card |
+----+------+--------------------+
| 1 | wang | 322343256564545754 |
+----+------+--------------------+
1 row in set (0.00 sec)
mysql>在b視窗對id=1的資料進行update name操作，發現失敗：等待鎖釋放超時
mysql> update testa set name = "wangwang" where id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction再對id=2的資料進行update name操作，發現成功
mysql> update testa set name = "shunshun" where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0a視窗commit；之後，b視窗update操作都顯示正常
- 無資料
a視窗 select for update 無資料
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from testa where id = 3
-> ;
Empty set (0.00 sec)
mysql>
b視窗，對兩條資料update操作都是成功
mysql> update testa set name = "wanga" where id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update testa set name = "shun" where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0得出結論
明確主鍵並且有資料的情況下:mysql -> row lock；
明確主鍵無資料的情況下:mysql -> no lock；
2.明確主鍵和一個普通欄位
- 有資料
將資料還原之後，
在a視窗進行開啟事務，對id=1,name=’wang’的資料進行 for update，此時並沒有commit；
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from testa where id=1 and name = 'wang' for update
-> ;
+----+------+--------------------+
| id | name | id_card |
+----+------+--------------------+
| 1 | wang | 322343256564545754 |
+----+------+--------------------+
1 row in set (0.03 sec)
mysql>b視窗，對進行for update的那條資料的update操作無效（等待鎖釋放超時），其他的行的update操作正常
mysql> update testa set name = "wanga" where id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update testa set name = "shunshun" where id = 2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0a視窗commit；之後，b視窗update操作都顯示成功
- 無資料
同第一種情況的無資料測試
得出結論
明確主鍵和一個普通欄位有資料的情況下:mysql -> row lock；
明確主鍵和一個普通欄位無資料的情況下:mysql -> no lock；
3.明確一個普通欄位
- 有資料
將資料還原之後，
在a視窗進行開啟事務，對name=’wang’的資料進行 for update，此時並沒有commit；
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from testa where name = 'wang' for update;
+----+------+--------------------+
| id | name | id_card |
+----+------+--------------------+
| 1 | wang | 322343256564545754 |
+----+------+--------------------+
1 row in set (0.00 sec)
mysql>b視窗，對進行for update的那條資料的update操作失敗（等待鎖釋放超時），其他的行的update操作也顯示失敗（等待鎖釋放超時）
mysql> update testa set id_card = '222' where id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update testa set id_card = '333' where id = 2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactiona視窗commit；之後，b視窗update操作都顯示成功
- 無資料
同第一種情況的無資料測試
得出結論
只明確一個普通欄位有資料的情況下:mysql -> table lock；
只明確一個普通欄位無資料的情況下:mysql -> no lock；
4.明確一個unique欄位
- 有資料
將資料還原之後，
在a視窗進行開啟事務，對id_card=’111’的資料進行 for update，此時並沒有commit；
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from testa where id_card='111' for update;
+----+------+---------+
| id | name | id_card |
+----+------+---------+
| 1 | wang | 111 |
+----+------+---------+
1 row in set (0.00 sec)
mysql>b視窗，對進行for update的那條資料的update操作失敗（等待鎖釋放超時），其他的行的update操作顯示正常！！
mysql> update testa set id_card = '222' where id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update testa set id_card = '333' where id = 2;
Query OK, 1 row affected (0.00 sec)- 無資料
同第一種情況的無資料測試
得出結論
只明確一個unique欄位有資料的情況下:mysql -> row lock；
只明確一個unique欄位無資料的情況下:mysql -> no lock；
思考
為什麼對主鍵和unique欄位進行for update操作的時候，mysql進行的是row lock；而對普通欄位for update操作的時候進行的是table lock，是根據什麼判斷呢？
primary key和unique的共同特點是mysql會自動為其建立索引，他們都有索引，那把name欄位建立索引，是不是就進行row lock呢？
檢視錶中的索引：
mysql> show keys from testa\G;
*************************** 1. row ***************************
Table: testa
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 2
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: testa
Non_unique: 0
Key_name: id_card
Seq_in_index: 1
Column_name: id_card
Collation: A
Cardinality: 2
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
2 rows in set (0.00 sec)
ERROR:
No query specified發現testa表中的索引只包含了id，id_card
新增name欄位的索引
mysql> alter table testa add index index_name (name);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0檢視建表語句：
mysql> show create table testa \G;
*************************** 1. row ***************************
Table: testa
Create Table: CREATE TABLE `testa` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) NOT NULL,
`id_card` varchar(18) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id_card` (`id_card`),
KEY `index_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
ERROR:
No query specified發現name欄位已經建立了普通索引index_name
在a視窗,對name欄位再進行一次for update測試,不commit
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from testa where name = 'wang' for update;
+----+------+---------+
| id | name | id_card |
+----+------+---------+
| 1 | wang | 222 |
+----+------+---------+
1 row in set (0.01 sec)
mysql>在b視窗 對進行for update的資料進行update操作失敗（鎖釋放等待超時）
mysql> update testa set id_card = '111' where id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction在b視窗 對其他行資料進行update操作,成功！！！
mysql> update testa set id_card = '4353' where id = 2;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0a視窗commit之後，在b敞口操作正常
總結
select … for update; 操作
未獲取到資料的時候，mysql不進行鎖 （no lock）
獲取到資料的時候，進行對約束欄位進行判斷，存在有索引的欄位則進行row lock
否則進行 table lock
注意
當使用 ‘<>’,‘like’等關鍵字時，進行for update操作時，mysql進行的是table lock
網上其他部落格說是因為主鍵不明確造成的，其實並非如此；
mysql進行row lock還是table lock只取決於是否能使用索引，而 使用’<>’,’like’等操作時，索引會失效，自然進行的是table lock；
什麼情況索引會失效:
1.負向條件查詢不能使用索引
負向條件有：!=、<>、not in、not exists、not like 等。
2.索引列不允許為null
單列索引不存null值，複合索引不存全為null的值，如果列允許為 null，可能會得到不符合預期的結果集。
3.避免使用or來連線條件
應該儘量避免在 where 子句中使用 or 來連線條件，因為這會導致索引失效而進行全表掃描，雖然新版的MySQL能夠命中索引，但查詢最佳化耗費的 CPU比in多。
4.模糊查詢
前導模糊查詢不能使用索引，非前導查詢可以。
以上情況索引都會失效，所以進行for update的時候，會進行table lock
參考：juejin.im/post/5b14e0fd6fb9a01e8c5...
再思考
為什麼存在索引，mysql進行row lock，不存在索引，mysql進行table lock？
這是儲存引擎InnoDB特性決定的：
InnoDB這種行鎖實現特點意味者：只有透過索引條件檢索資料，InnoDB才會使用行級鎖，否則，InnoDB將使用表鎖！
再總結
在上述例子中 ，我們使用給name欄位加索引的方法，使表鎖降級為行鎖，不幸的是這種方法只針對 屬性值重複率低 的情況。當屬性值重複率很高的時候，索引就變得低效，MySQL 也具有自動最佳化 SQL 的功能。低效的索引將被忽略。就會使用表鎖了。
注· 文章來源：慕課網《go開發工程師》體系課
本作品採用《CC 協議》，轉載必須註明作者和本文連結