前言
MySQL數(shù)據(jù)庫鎖是實(shí)現(xiàn)數(shù)據(jù)一致性，解決并發(fā)問題的重要手段。數(shù)據(jù)庫是一個(gè)多用戶共享的資源，當(dāng)出現(xiàn)并發(fā)的時(shí)候，就會(huì)導(dǎo)致出現(xiàn)各種各樣奇怪的問題，就像程序代碼一樣，出現(xiàn)多線程并發(fā)的時(shí)候，如果不做特殊控制的話，就會(huì)出現(xiàn)意外的事情，比如“臟“數(shù)據(jù)、修改丟失等問題。所以數(shù)據(jù)庫并發(fā)需要使用事務(wù)來控制，事務(wù)并發(fā)問題需要數(shù)據(jù)庫鎖來控制，所以數(shù)據(jù)庫鎖是跟并發(fā)控制和事務(wù)聯(lián)系在一起的。
本文主要描述基于更新SQL語句來理解MySQL鎖定。下面話不多說了，來一起看看詳細(xì)的介紹吧
一、構(gòu)造環(huán)境
(root@localhost) [user]> show variables like 'version';+---------------+------------+| Variable_name | Value |+---------------+------------+| version | 5.7.23-log |+---------------+------------+(root@localhost) [user]> desc t1;+-------------+--------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-------------+--------------+------+-----+---------+----------------+| id | int(11) | NO | PRI | NULL | auto_increment || n | int(11) | YES | | NULL | || table_name | varchar(64) | YES | | NULL | || column_name | varchar(64) | YES | | NULL | || pad | varchar(100) | YES | | NULL | |+-------------+--------------+------+-----+---------+----------------+(root@localhost) [user]> select count(*) from t1;+----------+| count(*) |+----------+| 3406 |+----------+(root@localhost) [user]> create unique index idx_t1_pad on t1(pad);Query OK, 0 rows affected (0.35 sec)Records: 0 Duplicates: 0 Warnings: 0(root@localhost) [user]> create index idx_t1_n on t1(n);Query OK, 0 rows affected (0.03 sec)Records: 0 Duplicates: 0 Warnings: 0(root@localhost) [user]> show index from t1;+-------+------------+------------+--------------+-------------+-----------+-------------+------+------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Null | Index_type |+-------+------------+------------+--------------+-------------+-----------+-------------+------+------------+| t1 | 0 | PRIMARY | 1 | id | A | 3462 | | BTREE || t1 | 0 | idx_t1_pad | 1 | pad | A | 3406 | YES | BTREE || t1 | 1 | idx_t1_n | 1 | n | A | 12 | YES | BTREE |+-------+------------+------------+--------------+-------------+-----------+-------------+------+------------+select 'Leshami' author,'http://blog.csdn.net/leshami' Blog;+---------+------------------------------+| author | Blog |+---------+------------------------------+| Leshami | http://blog.csdn.net/leshami |+---------+------------------------------+
二、基于主鍵更新
(root@localhost) [user]> start transaction;Query OK, 0 rows affected (0.00 sec)(root@localhost) [user]> update t1 set table_name='t1' where id=1299;Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id, trx_tables_locked, trx_rows_locked, trx_rows_modified, trx_isolation_levelFROM INFORMATION_SCHEMA.INNODB_TRX /G-- 從下面的結(jié)果可知，trx_rows_locked，一行被鎖定 *************************** 1. row *************************** trx_id: 6349647 trx_state: RUNNING trx_started: 2018-11-06 16:54:12trx_mysql_thread_id: 2 trx_tables_locked: 1 trx_rows_locked: 1 trx_rows_modified: 1trx_isolation_level: REPEATABLE READ (root@localhost) [user]> rollback;Query OK, 0 rows affected (0.01 sec)
三、基于二級(jí)唯一索引
(root@localhost) [user]> start transaction;Query OK, 0 rows affected (0.00 sec)(root@localhost) [user]> update t1 set table_name='t2' where pad='4f39e2a03df3ab94b9f6a48c4aecdc0b';Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id, trx_tables_locked, trx_rows_locked, trx_rows_modified, trx_isolation_levelFROM INFORMATION_SCHEMA.INNODB_TRX /G-- 從下面的查詢結(jié)果可知，trx_rows_locked，2行被鎖定*************************** 1. row *************************** trx_id: 6349649 trx_state: RUNNING trx_started: 2018-11-06 16:55:22trx_mysql_thread_id: 2 trx_tables_locked: 1 trx_rows_locked: 2 trx_rows_modified: 1trx_isolation_level: REPEATABLE READ (root@localhost) [user]> rollback;Query OK, 0 rows affected (0.00 sec)
三、基于二級(jí)非唯一索引
(root@localhost) [user]> start transaction;Query OK, 0 rows affected (0.00 sec)(root@localhost) [user]> update t1 set table_name='t3' where n=8;Query OK, 350 rows affected (0.01 sec)Rows matched: 351 Changed: 351 Warnings: 0SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id, trx_tables_locked, trx_rows_locked, trx_rows_modified, trx_isolation_levelFROM INFORMATION_SCHEMA.INNODB_TRX /G --從下面的查詢結(jié)果可知，703行被鎖定*************************** 1. row *************************** trx_id: 6349672 trx_state: RUNNING trx_started: 2018-11-06 17:06:53trx_mysql_thread_id: 2 trx_tables_locked: 1 trx_rows_locked: 703 trx_rows_modified: 351trx_isolation_level: REPEATABLE READ(root@localhost) [user]> rollback;Query OK, 0 rows affected (0.00 sec)
四、無索引更新
(root@localhost) [user]> start transaction;Query OK, 0 rows affected (0.00 sec)(root@localhost) [user]> update t1 set table_name='t4' where column_name='id';Query OK, 26 rows affected (0.00 sec)Rows matched: 26 Changed: 26 Warnings: 0SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id, trx_tables_locked, trx_rows_locked, trx_rows_modified, trx_isolation_levelFROM INFORMATION_SCHEMA.INNODB_TRX /G-- 從下面的查詢結(jié)果可知，trx_rows_locked，3429行被鎖定，而被更新的僅僅為26行-- 而且這個(gè)結(jié)果超出了表上的總行數(shù)3406*************************** 1. row *************************** trx_id: 6349674 trx_state: RUNNING trx_started: 2018-11-06 17:09:41trx_mysql_thread_id: 2 trx_tables_locked: 1 trx_rows_locked: 3429 trx_rows_modified: 26trx_isolation_level: REPEATABLE READ(root@localhost) [user]> rollback;Query OK, 0 rows affected (0.00 sec)-- 也可以通過show engine innodb status進(jìn)行觀察show engine innodb status/G------------TRANSACTIONS------------Trx id counter 6349584Purge done for trx's n:o < 0 undo n:o < 0 state: running but idleHistory list length 0LIST OF TRANSACTIONS FOR EACH SESSION:---TRANSACTION 421943222819552, not started0 lock struct(s), heap size 1136, 0 row lock(s)---TRANSACTION 6349583, ACTIVE 2 sec2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1------------TRANSACTIONS------------Trx id counter 6349586Purge done for trx's n:o < 6349585 undo n:o < 0 state: running but idleHistory list length 1LIST OF TRANSACTIONS FOR EACH SESSION:---TRANSACTION 421943222819552, not started0 lock struct(s), heap size 1136, 0 row lock(s)---TRANSACTION 6349585, ACTIVE 8 sec3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1MySQL thread id 2, OS thread handle 140467640694528, query id 29 localhost root
五、鎖相關(guān)查詢SQL
1：查看當(dāng)前的事務(wù)
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
2：查看當(dāng)前鎖定的事務(wù)
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
3：查看當(dāng)前等鎖的事務(wù)
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id thr_id, trx_tables_locked tb_lck, trx_rows_locked rows_lck, trx_rows_modified row_mfy, trx_isolation_level is_lvlFROM INFORMATION_SCHEMA.INNODB_TRX;SELECT r.`trx_id` waiting_trx_id, r.`trx_mysql_thread_id` waiting_thread, r.`trx_query` waiting_query, b.`trx_id` bolcking_trx_id, b.`trx_mysql_thread_id` blocking_thread, b.`trx_query` block_queryFROM information_schema.`INNODB_LOCK_WAITS` w INNER JOIN information_schema.`INNODB_TRX` b ON b.`trx_id` = w.`blocking_trx_id` INNER JOIN information_schema.`INNODB_TRX` r ON r.`trx_id` = w.`requesting_trx_id`;
六、小結(jié)
1、MySQL表更新時(shí)，對(duì)記錄的鎖定根據(jù)更新時(shí)where謂詞條件來確定鎖定范圍
2、對(duì)于聚簇索引過濾，由于索引即數(shù)據(jù)，因?yàn)閮H僅鎖定更新行，這是由聚簇索引的性質(zhì)決定的
3、對(duì)于非聚簇唯一索引過濾，由于需要回表，因此鎖定為唯一索引過濾行數(shù)加上回表行數(shù)
4、對(duì)于非聚簇非唯一索引過濾，涉及到了間隙鎖，因此鎖定的記錄數(shù)更多
5、如果過濾條件無任何索引或無法使用到索引，則鎖定整張表上所有數(shù)據(jù)行
總結(jié)
以上就是這篇文章的全部內(nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，如果有疑問大家可以留言交流，謝謝大家對(duì)VeVb武林網(wǎng)的支持。
新聞熱點(diǎn)
疑難解答
圖片精選
