前言:
Shared and Exclusive Locks译称共享锁/排他锁,本文参考官方文档进行学习说明外加实验例子
数据库版本:
SELECT VERSION();
±-----------+
| version() |
±-----------+
| 5.6.34-log |
±-----------+
数据库引擎:
show variables like ‘%engine%’;
±---------------------------±-------+
| Variable_name | Value |
±---------------------------±-------+
| default_storage_engine | InnoDB |
| default_tmp_storage_engine | InnoDB |
| storage_engine | InnoDB |
±---------------------------±-------+
根据版本信息参考官方文档:
1.MySQL 5.6 Reference Manual-InnoDB Shared and Exclusive Locks(共享锁-排他锁)
共享锁-排他锁
innodb实现了标准的行级锁,包括两种锁模式:S(共享锁)、X(排他锁)
InnoDB implements standard row-level locking where there are two types of locks, shared (S) locks and exclusive (X) locks.
(1)事务拿到某一行记录的共享S锁,才可以读取这一行;即锁读
(2)事务拿到某一行记录的排它X锁,才可以修改或者删除这一行;
A shared (S) lock permits the transaction that holds the lock to read a row.
An exclusive (X) lock permits the transaction that holds the lock to update or delete a row.
共享锁和排他锁的兼容矩阵:
S | X | |
---|---|---|
S | 兼容 | 冲突 |
X | 冲突 | 冲突 |
上面的兼容矩阵都是针对同一行记录进行测试得出的结论。
共享锁加锁方式:
select lock in share mode;
排他锁加锁方式:
select for update/ update/ delete