专栏首页乐沙弥的世界MySQL SQL更新锁定

MySQL SQL更新锁定

版权声明:本文为博主原创文章,欢迎扩散,扩散请务必注明出处。

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: 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、如果过滤条件无任何索引或无法使用到索引,则锁定整张表上所有数据行

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 悲观锁&乐观锁

    最近意外发现之前对悲观锁乐观锁的理解有误,所以重新学习了一下。 1.悲观锁 悲观锁介绍(百科): 悲观锁,正如其名,它指的是对数据被外界(包括本系统当前的其他事...

    老白
  • MySQL 加锁处理分析

    背景 MySQL/InnoDB的加锁分析,一直是一个比较困难的话题。我在工作过程中,经常会有同事咨询这方面的问题。同时,微博上也经常会收到MySQL锁相关的私信...

    java达人
  • 炸裂!MySQL 82 张图带你飞!

    之前两篇文章带你了解了 MySQL 的基础语法和 MySQL 的进阶内容,相关链接如下

    cxuan
  • DBbrain诊断日 | 不这么办,数据库敢崩个三天三夜给你看

    为更好的帮助DBA运维数据库,腾讯云将于每月12日开展DBbrain诊断日,腾讯云高级产品经理迪B哥直播解析经典数据库运维难题,结合腾讯云数据库智能管家DBb...

    腾讯云数据库 TencentDB
  • MySQL详解--锁

    锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中,除传统的计算资源(如CPU、RAM、I/O等)的争用以外,数据也是一种供许多用户共享的资源。如...

    bear_fish
  • MySQL 8.0发布了,来看看针对SQL有哪些新功能?

    据官方称,此次发布意味着MySQL从此兼顾NoSQL和SQL于一身。NoSQL+SQL=MySQL。

    ImportSource
  • Mysql查询语句使用select.. for update导致的数据库死锁分析

    近期有一个业务需求,多台机器需要同时从Mysql一个表里查询数据并做后续业务逻辑,为了防止多台机器同时拿到一样的数据,每台机器需要在获取时锁住获取数据的数据段,...

    李红
  • Mysql之锁与事务

    平时的业务中,顶多也就是写写简单的sql,连事务都用的少,对锁这一块的了解就更加欠缺了,之前一个大神分享了下mysql的事务隔离级别,感觉挺有意思的,正好发现一...

    一灰灰blog
  • 看MySQL的参数调优及数据库锁实践有这一篇足够了

    对于访问数据库来说,建立连接的代价是比较昂贵的,因为我们频繁的创建关闭连接,是比较耗费资源的,我们有必要建立数据库连接池,以提高访问的性能。

    海仔

扫码关注云+社区

领取腾讯云代金券