MySQL SQL更新ロック
8442 ワード
MySQLデータベース・ロックは、データの一貫性を実現し、同時性の問題を解決する重要な手段です.データベースはマルチユーザが共有するリソースであり、コンカレントが発生すると様々な奇妙な問題が発生し、プログラムコードのようにマルチスレッドが同時発生する場合、特別な制御をしないと意外なことが起こります.たとえば、「汚い」、「データの変更」、「失われた」などの問題があります.したがって、データベースの同時実行にはトランザクションを使用して制御する必要があり、トランザクションの同時実行にはデータベース・ロックを使用して制御する必要があるため、データベース・ロックは同時実行制御とトランザクションに関連付けられています.ここでは、SQL文の更新に基づいてMySQLロックを理解することについて説明します.
一、構造環境
二、主キーに基づく更新
三、二次一意索引に基づく
三、二次非一意索引に基づく
四、インデックス更新なし
五、ロック関連クエリーSQL
六、まとめ
1、MySQLテーブルの更新時、記録に対するロックは更新時のwhere述語条件によってロック範囲を決定する2、クラスタ化インデックスに対してフィルタリングする、インデックス即ちデータのため、更新行のみをロックするので、これはクラスタ化インデックスの性質によって決定される3、クラスタ化されていない一意インデックスに対してフィルタリングされ、テーブルに戻る必要があるため、ロックは一意インデックスフィルタ行数に回表行数4を加え、クラスタ化されていない一意インデックスフィルタリングに対して、クリアランスロックに関連しているため、ロックされたレコード数が5より多く、フィルタ条件にインデックスがないか、インデックスが使用できない場合は、テーブル全体のすべてのデータ行をロックします.
一、構造環境
(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: 0
SELECT trx_id,
trx_state,
trx_started,
trx_mysql_thread_id,
trx_tables_locked,
trx_rows_locked,
trx_rows_modified,
trx_isolation_level
FROM INFORMATION_SCHEMA.INNODB_TRX \G
-- ，trx_rows_locked，
*************************** 1. row ***************************
trx_id: 6349647
trx_state: RUNNING
trx_started: 2018-11-06 16:54:12
trx_mysql_thread_id: 2
trx_tables_locked: 1
trx_rows_locked: 1
trx_rows_modified: 1
trx_isolation_level: REPEATABLE READ
(root@localhost) [user]> rollback;
Query OK, 0 rows affected (0.01 sec)
三、二次一意索引に基づく
(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: 0
SELECT trx_id,
trx_state,
trx_started,
trx_mysql_thread_id,
trx_tables_locked,
trx_rows_locked,
trx_rows_modified,
trx_isolation_level
FROM INFORMATION_SCHEMA.INNODB_TRX \G
-- ，trx_rows_locked，2
*************************** 1. row ***************************
trx_id: 6349649
trx_state: RUNNING
trx_started: 2018-11-06 16:55:22
trx_mysql_thread_id: 2
trx_tables_locked: 1
trx_rows_locked: 2
trx_rows_modified: 1
trx_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='t3' where n=8;
Query OK, 350 rows affected (0.01 sec)
Rows matched: 351 Changed: 351 Warnings: 0
SELECT trx_id,
trx_state,
trx_started,
trx_mysql_thread_id,
trx_tables_locked,
trx_rows_locked,
trx_rows_modified,
trx_isolation_level
FROM INFORMATION_SCHEMA.INNODB_TRX \G
-- ，703
*************************** 1. row ***************************
trx_id: 6349672
trx_state: RUNNING
trx_started: 2018-11-06 17:06:53
trx_mysql_thread_id: 2
trx_tables_locked: 1
trx_rows_locked: 703
trx_rows_modified: 351
trx_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: 0
SELECT trx_id,
trx_state,
trx_started,
trx_mysql_thread_id,
trx_tables_locked,
trx_rows_locked,
trx_rows_modified,
trx_isolation_level
FROM INFORMATION_SCHEMA.INNODB_TRX \G
-- ，trx_rows_locked，3429 ， 26
-- 3406
*************************** 1. row ***************************
trx_id: 6349674
trx_state: RUNNING
trx_started: 2018-11-06 17:09:41
trx_mysql_thread_id: 2
trx_tables_locked: 1
trx_rows_locked: 3429
trx_rows_modified: 26
trx_isolation_level: REPEATABLE READ
(root@localhost) [user]> rollback;
Query OK, 0 rows affected (0.00 sec)
-- show engine innodb status
show engine innodb status\G
------------
TRANSACTIONS
------------
Trx id counter 6349584
Purge done for trx's n:o < 0 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421943222819552, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 6349583, ACTIVE 2 sec
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
------------
TRANSACTIONS
------------
Trx id counter 6349586
Purge done for trx's n:o < 6349585 undo n:o < 0 state: running but idle
History list length 1
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421943222819552, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 6349585, ACTIVE 8 sec
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 2, OS thread handle 140467640694528, query id 29 localhost root
五、ロック関連クエリーSQL
1：
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
2：
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
3：
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_lvl
FROM 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_query
FROM 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`;
六、まとめ
1、MySQLテーブルの更新時、記録に対するロックは更新時のwhere述語条件によってロック範囲を決定する2、クラスタ化インデックスに対してフィルタリングする、インデックス即ちデータのため、更新行のみをロックするので、これはクラスタ化インデックスの性質によって決定される3、クラスタ化されていない一意インデックスに対してフィルタリングされ、テーブルに戻る必要があるため、ロックは一意インデックスフィルタ行数に回表行数4を加え、クラスタ化されていない一意インデックスフィルタリングに対して、クリアランスロックに関連しているため、ロックされたレコード数が5より多く、フィルタ条件にインデックスがないか、インデックスが使用できない場合は、テーブル全体のすべてのデータ行をロックします.