基于更新SQL语句理解MySQL锁定解说
发布时间：2022-03-05 08:08:15 所属栏目：MySql教程 来源：互联网
导读：MySQL数据库锁是实现数据一致性，解决并发问题的重要手段。数据库是一个多用户共享的资源，当出现并发的时候，就会导致出现各种各样奇怪的问题，就像程序代码一样，出现多线程并发的时候，如果不做特殊控制的话，就会出现意外的事情，比如脏数据、修改丢失等
|
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]> 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 ------------ 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 （编辑：晋中站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
