【holm】MySQL锁机制

lpfvip00

2020-05-07

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”）是一种并发控制的方法。它假设多用户并发的事务在处理时不会彼此互相影响，各事务能够在不产生锁的情况下处理各自影响的那部分数据。在提交数据更新之前，每个事务会先检查在该事务读取数据后，有没有其他事务又修改了该数据。如果其他事务有更新的话，正在提交的事务会进行回滚。
悲观锁比较适合写入操作比较频繁的场景，如果出现大量的读取操作，每次读取的时候都会进行加锁，这样会增加大量的锁的开销，降低了系统的吞吐量。
乐观锁比较适合读取操作比较频繁的场景，如果出现大量的写入操作，数据发生冲突的可能性就会增大，为了保证数据的一致性，应用层需要不断的重新获取数据，这样会增加大量的查询操作，降低了系统的吞吐量。