MySQL锁机制

 

MySQL锁机制

1.概述

1.从性能上，分为乐观锁和悲观锁。乐观锁用版本比对来实现

2.从对数据库操作的类型，分为读锁和写锁，读锁和写锁都属于悲观锁。

读锁（共享锁，S锁（Shared））：针对同一份数据，多个读操作可以同时进行而不互相影响。读锁不会阻塞其他读锁但会阻塞其他写锁。

事务A：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user where id=1 lock in share mode;
+----+--------+------+--------+
| id | name   | age  | gender |
+----+--------+------+--------+
|  1 | 叶涵   |   22 |      0 |
+----+--------+------+--------+
1 row in set (0.00 sec)

mysql>

事务B

mysql> select * from user where id=1 lock in share mode;
+----+--------+------+--------+
| id | name   | age  | gender |
+----+--------+------+--------+
|  1 | 叶涵   |   22 |      0 |
+----+--------+------+--------+
1 row in set (0.00 sec)

mysql> select * from user where id=1 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>

写锁（排他锁/独占锁，X锁（eXclusive））：当前写操作没有操作完前即事务没有提交，不影响其他读操作，但会阻塞其他读锁和写锁。

事务A：
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user where id=1 for update;
+----+--------+------+--------+
| id | name   | age  | gender |
+----+--------+------+--------+
|  1 | 叶涵   |   22 |      0 |
+----+--------+------+--------+
1 row in set (0.00 sec)

mysql>

 

事务B：
mysql> select * from user where id=1;
+----+--------+------+--------+
| id | name   | age  | gender |
+----+--------+------+--------+
|  1 | 叶涵   |   22 |      0 |
+----+--------+------+--------+
1 row in set (0.00 sec)

mysql> select * from user where id=1 lock in share mode;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> select * from user where id=1 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>

3.从对数据库操作的粒度，分为表锁和行锁（页锁不讨论）

2.表锁

锁的粒度最大，每次操作都会锁住整张表，发生锁冲突的概率最高，并发度低；加锁快，开销小，不会出现死锁。一般用在整张表迁移的场景。

• 手动增加表锁

LOCK TABLE table_name  READ    共享锁

LOCK TABLE table_name  WRITE  排他锁

• 查看表上加过的锁

show  open tables;

• 解除表锁

unlock tables;

3.行锁

细粒度的锁，只针对当前操作的行进行加锁，发生锁冲突的概率低，并发度高；加锁开销大，会出现死锁。