前言
MySQL數據庫鎖是實現數據一致性，解決并發問題的重要手段。數據庫是一個多用戶共享的資源，當出現并發的時候，就會導致出現各種各樣奇怪的問題，就像程序代碼一樣，出現多線程并發的時候，如果不做特殊控制的話，就會出現意外的事情，比如“臟“數據、修改丟失等問題。所以數據庫并發需要使用事務來控制，事務并發問題需要數據庫鎖來控制，所以數據庫鎖是跟并發控制和事務聯系在一起的。
本文主要描述基于更新SQL語句來理解MySQL鎖定。下面話不多說了，來一起看看詳細的介紹吧
一、構造環境
| (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-- 從下面的結果可知，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) |
