【holm】MySQL锁机制

相关概念

• 粒度：锁的作用范围
  • 表级
  • 行级
• 显示锁
• 隐式锁
• 表级锁的类型
  • 读锁(read lock)
    • 也称为共享锁(Share Locks，S锁)，多个事务可以同时拥有共享锁；但是不能再获取排它锁。
    • 读锁允许其它MySQL客户机对数据同时“读”，但不允许其它MySQL客户机对数据任何“写”。
  • 写锁(write lock)
    • 也称为排他锁(Exclusive Locks，X锁)或者独占锁同一时刻只能有一个事务拥有排它锁，其它事务不能拥有共享锁和排它锁。
    • 写锁不允许其它MySQL客户机对数据同时“读”，也不允许其它MySQL客户机对数据同时“写”。
• 行级锁的类型
  • 共享锁(Share Locks，S锁)
  • 排他锁(Exclusive Locks，X锁)
  • InnoDB引擎insert、update、delete会自动给涉及的数据排他锁(隐式锁)；对于一般的Select语句不会加任何锁。
• 锁的生命周期：在同一个MySQL服务器连接内，对数据加锁到解锁之间的时间间隔。
• 意向锁：定义行级锁自动给当前表加的表级锁，用于说明当前表的锁兼容性，
  • 意向共享锁(IS)
  • 意向排他锁(IX)
• 死锁：事务内加锁后因为某些原因不能顺利运行，直到锁等待超时异常，此时不会提交事务也不会回滚事务。
• 悲观锁；直接使用排他锁锁住某条记录使其他客户机不能修改此条。
• 乐观锁：在这条记录上加一个version字段，更新的时候就+1，select的时候带出这个字段，当实际更新的时候判断当前version是不是等于记录中的version，是则成功，反之失败回退。

Syntax

表级锁

• 用读锁锁表，会阻塞其他事务修改表数据。
  LOCK TABLE my_tabl_name READ;
• 用写锁锁表，会阻塞其他事务读和写。
  LOCK TABLE my_table_name WRITe;
• 解锁-1

show processlist; --如果有SUPER权限，可以看到所有线程。否则，只能看到自己的线程
kill id; --id是processlist中锁进程id

• 解锁-2
  UNLOCK TABLES;
• 查询是否锁表
  show OPEN TABLES where In_use > 0;

行级锁

• 读锁(x)
  select …… for share;
select …… lock in share mode; --仍能使用
• 写锁(s)
  select …… for update;

其他

注意

• InnoDB表的行级锁是通过对“索引”施加锁的方式实现的，这就意味着：只有通过索引字段检索数据的查询语句或者更新语句，才可能施加行级锁；否则InnoDB将使用表级锁，使用表级锁势必会降低InnoDB表的并发访问性能。
• 被加锁的表如果有触发器则与此触发器相关的表都会被加上隐式锁
• 为了避免等待其他事务释放行锁，可以将NOWAIT和SKIP LOCKED选项与SELECT ... FOR UPDATE或SELECT ... FOR SHARE锁定读取语句一起使用。

# Session 1:

mysql> CREATE TABLE t (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;

mysql> INSERT INTO t (i) VALUES(1),(2),(3);

mysql> START TRANSACTION;

mysql> SELECT * FROM t WHERE i = 2 FOR UPDATE;
+---+
| i |
+---+
| 2 |
+---+

# Session 2:

mysql> START TRANSACTION;

mysql> SELECT * FROM t WHERE i = 2 FOR UPDATE NOWAIT;
ERROR 3572 (HY000): Do not wait for lock.

# Session 3:

mysql> START TRANSACTION;

mysql> SELECT * FROM t FOR UPDATE SKIP LOCKED;
+---+
| i |
+---+
| 1 |
| 3 |
+---+          

悲观锁和乐观锁

• 在关系数据库管理系统里，悲观并发控制（又名”悲观锁”，Pessimistic Concurrency Control，缩写”PCC”）是一种并发控制的方法。它可以阻止一个事务以影响其他用户的方式来修改数据。如果一个事务执行的操作读某行数据应用了 锁，那只有当这个事务把锁释放，其他事务才能够执行与该锁冲突的操作。
• 乐观并发控制（又名”乐观锁”，Optimistic Concurrency Control，缩写”OCC”）是一种并发控制的方法。它假设多用户并发的事务在处理时不会彼此互相影响，各事务能够在不产生锁的情况下处理各自影响的 那部分数据。在提交数据更新之前，每个事务会先检查在该事务读取数据后，有没有其他事务又修改了该数据。如果其他事务有更新的话，正在提交的事务会进行回滚。
• 悲观锁比较适合写入操作比较频繁的场景，如果出现大量的读取操作，每次读取的时候都会进行加锁，这样会增加大量的锁的开销，降低了系统的吞吐量。
• 乐观锁比较适合读取操作比较频繁的场景，如果出现大量的写入操作，数据发生冲突的可能性就会增大，为了保证数据的一致性，应用层需要不断的重新获取数据，这样会增加大量的查询操作，降低了系统的吞吐量。

参考资料

• mysql doc
  • 8.11.1 Internal Locking Methods
  • 13.3.6 LOCK TABLES and UNLOCK TABLES Syntax
  • 13.7.6.24 SHOW OPEN TABLES Syntax
  • 15.7.2.4 Locking Reads
• 课堂资料-_-
• https://www.cnblogs.com/qlqwjy/p/7798266.html
• https://blog.csdn.net/qq_34337272/article/details/81072874
• https://blog.csdn.net/L_BestCoder/article/details/79298417
• https://www.cnblogs.com/cndarren/p/11787286.html