MySQL锁相关语句和操作

最新推荐文章于 2023-01-31 14:40:44 发布

ing X

最新推荐文章于 2023-01-31 14:40:44 发布

阅读量277

点赞数

分类专栏： mysql 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/qq_35968173/article/details/128455802

版权

mysql 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

显式加锁语句

共享锁：
语句后接： lock in share mode
8.0： for share
例：select * from table where id=1 for share

排他锁：
语句后接： for update
例：select * from table  where id=1 for update

修饰词：
nowait:不等待直接报错返回。
skip locked:跳过被锁定的记录，返回未上锁的记录。
例：假设5条记录，事务一锁定了3行,id=（1，2，3）。事务一和事务二至少存在一个x锁。
tx1:
	select * from table_a where id in (1,2,3) for update;
tx2:
	select * from table_a for share/update; ---阻塞至超时
	select * from table_a for share/update nowait; ---直接报错返回
	select * from table_a for share/update skip locked; ---返回id为4，5的记录

注意：加锁时条件字段必须有索引，如果全表扫描会给扫描行全部加锁。

表锁语句

查看是否有表锁,in_use字段：
show open tables;
mysql> show open tables;
+--------------------+---------------------------+--------+-------------+
| Database           | Table                     | In_use | Name_locked |
+--------------------+---------------------------+--------+-------------+
| mysql              | check_constraints         |      0 |           0 |
| mysql              | column_type_elements      |      0 |           0 |
| mysql              | slave_master_info         |      0 |           0 |

加锁：
读锁：lock tables `table_a` read;
写锁：lock tables `table_a` write;

解锁：
unlock tables;

表锁后的操作

全局锁语句

对整个数据库加锁，只读。场景：全库逻辑备份
加锁：
flush tables with read lock;

解锁：
unlock tables;

查看锁相关语句

show status like 'innodb_row_lock%';

mysql> show status like 'innodb_row_lock%';
+-------------------------------+--------+
| Variable_name                 | Value  |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 1      |
| Innodb_row_lock_time          | 107546 |
| Innodb_row_lock_time_avg      | 10754  |
| Innodb_row_lock_time_max      | 17256  |
| Innodb_row_lock_waits         | 10     |
+-------------------------------+--------+

5.7
information_schema库下innodb_trx、innodb_locks、innodb_lock_waits
8.0
information_schema库下innodb_trx，performance_schema库下data_locks、data_lock_waits

例：
mysql> select * from performance_schema.data_locks\G;
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 2800795744848:1156:2800761520952
ENGINE_TRANSACTION_ID: 15403
            THREAD_ID: 52
             EVENT_ID: 19
        OBJECT_SCHEMA: test_db
          OBJECT_NAME: testa
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 2800761520952
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 2800795744848:99:4:15:2800761518856
ENGINE_TRANSACTION_ID: 15403
            THREAD_ID: 52
             EVENT_ID: 21
        OBJECT_SCHEMA: test_db
          OBJECT_NAME: testa
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 2800761518856
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
          LOCK_STATUS: WAITING
            LOCK_DATA: 1