MySql lock
MySQL 锁
表级锁 和 行级锁
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| mylock |
| test |
+----------------+
2 rows in set (0.03 sec)
表锁
表锁有两种
元数据锁（Meta Data Lock，MDL）
查询争用状态变量
show status like 'table_locks%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Table_locks_immediate | 104 |
| Table_locks_waited | 0 |
+-----------------------+-------+
2 rows in set (0.04 sec)
Table_locks_immediate：产生表级锁定的次数Table_locks_waited：出现表级锁定争用而发生等待的请求数#### 表锁两种形式lock table 表名称1 read(write)
开启
read其他select...可以查但是不能修改会自动加入行锁开启
write其他select ....会查询锁住
- 表共享读锁（Table Read Lock）
- session1:
lock table mylock read;给 mylock 表加读锁 - session1:
select * from mylock;可以查询 - session1：
select * from tdep;不能访问非锁定表(1100 - Table ‘test’ was not locked with LOCK TABLES) - session2：
select * from mylock;可以查询 没有锁 - session2：
update mylock set name='x' where id=2;修改阻塞,自动加行写锁 - session1：
unlock tables;释放表锁 - session2：
Rows matched: 1 Changed: 1 Warnings: 0修改执行完成 - session1：
select * from tdep;可以访问
- 表独占写锁（Table Write Lock）
- session1:
lock table mylock write;给 mylock 表加写锁 - session1:
select * from mylock;当前 session 可以查询 - session1：
select * from tdep;不能访问非锁定表 - session1：
update mylock set name='y' where id=2;可以执行 - session2：
select * from mylock;查询阻塞 - session1：
unlock tables;释放表锁 - session2：
4 rows in set (22.57 sec)查询执行完成 - session1：
select * from tdep;可以访问
- 元数据锁（MDL）
MetaDataLock简单讲就是数据库存储的数据或者表结构，可以通过
show或者select来查看 ，但是元数据锁 在mysql5.5引进的对metadata对事务级别保护，5.5之前是语句级别。所谓语句级别，即语句执行完成后，无论事务是否提交或回滚，其表结构可以被其他会话更新；而事务级别则是在事务结束后才释放MDL，引进MDL主要解决两个问题
- 一个是事务隔离问题，比如在
可重复隔离级别下，会话 A 在 2 次查询期间，会话 B 对表结构做了修改，两次查询结果就会不一致，无法满足可重 复读的要求； - 另外一个是
数据复制的问题，比如会话 A 先执行了更新语句期间，另外一个会话 B 做了表结构变更并且先提交，就会导致 slave 在重做时，先重做 alter，再重做 update 时就会出现复制错误的现象
.png)
session1: begin;开启事务select * from mylock;加 MDL 读锁session2: alter table mylock add f int;修改阻塞session1：commit;提交事务 或者 rollback 释放读锁session2：Query OK, 0 rows affected (38.67 sec)修改完成
行锁(InnoDB)
按照范围分类
记录锁、间隙锁、Next-Key锁
记录锁（Record Locks）:锁定索引中一条记录。 例如主键或唯一索引等值条件指定 where id=3间隙锁（Gap Locks）: 锁定索引记录之间的间隙，或者是某一条记录之前和之后加锁，该机制主要为了解决幻读问题。Next-Key 锁: 记录锁 + 间隙锁，RR 隔离级下默认使用 Next-Key 锁，即锁定当前记录也锁定记录间隙。
按照功能分类
共享读锁（S）、排他写锁（X）
-
共享读锁（行读锁）
- 行级锁
1、session1:
begin;开启事务未提交select * from mylock where id=1 lock in share mode;手动加 id=1 的行读锁,使用索引 2、session2：update mylock set name='y' where id=2;未锁定该行可以修改 3、session2：update mylock set name='y' where id=1;锁定该行修改阻塞ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction锁定超时4、session1:
commit;提交事务 或者 rollback 释放读锁 5、session2：update mylock set name=’y’ where id=1;` 修改成功-
行级锁升级为表锁（未使用索引行锁升级为表锁）
- session1:
begin;开启事务未提交select * from mylock where id=1 lock in share mode;手动加 id=1 的行读锁,使用索引 - session2：
update mylock set name='y' where id=2;未锁定该行可以修改 - session2：
update mylock set name='y' where id=1;锁定该行修改阻塞ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction– 锁定超时 - session1:
commit;–提交事务 或者 rollback 释放读锁 - session2：
update mylock set name='y' where id=1;–修改成功
- session1:
-
排他写锁（行写锁）
行产生写锁时，在MVCC下仍然可读，因为MVCC下的select读不需要获取锁（无锁），所以不会阻塞- session1:
begin;开启事务未提交,手动加”id=1”的行写锁,select * from mylock where id=1 for update; - session2：
select * from mylock where id=2 ;可以访问 - session2:
select * from mylock where id=1 ;可以读 不加锁 - session2:
select * from mylock where id=1 lock in share mode ;加读锁被阻塞 - session1：
commit;提交事务 或者rollback释放写锁 - session2：执行成功
- session1:
-
间隙锁
在索引记录之间的间隙中加锁，或者是在某一条索引记录之前或者之后加锁，并不包括该索引记录本身。gap lock 的机制主要是
解决可重复读模式下的的幻读问题
死锁
两个 session 互相等待 对方资源释放之后才能释放自己资源造成死锁
- session1:
begin;开启事务未提交,手动加行写锁 id=1 ，使用索引 `update mylock set name=’m’ where id=1; - session2：
begin;开启事务未提交,手动加行写锁id=2使用索引update mylock set name='m' where id=2; - session1:
update mylock set name='nn' where id=2;加写锁被阻塞 - session2：
update mylock set name='nn' where id=1;加写锁会死锁，不允许操作
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting
transaction
避免死锁
越是轻量的事务，占有越少的锁资源，这样发生死锁的几率就越小 1.避免使用子查询，尽量使用主键等等，以 提高SQL运行速度 2.尽量快提交事务，减少持有锁的时间