【MySQL】锁

1、锁的相关查看

查看事务

select * from  INFORMATION_SCHEMA.INNODB_TRX;

TRX_ID    事务Id
TRX_WEIGHT    事务的权重，反映（但不一定是确切的计数）更改的行数和事务锁定的行数。要解决死锁，请 InnoDB选择权重最小的事务作为回滚的“ 受害者 ”。无论更改和锁定行的数量如何，已更改非事务表的事务都被认为比其他事务更重。
TRX_STATE    事务执行状态。允许值是 RUNNING，LOCK WAIT， ROLLING BACK，和 COMMITTING。
TRX_STARTED    交易开始时间。
TRX_REQUESTED_LOCK_ID    事务当前正在等待的锁的ID，如果TRX_STATE是LOCK WAIT; 否则NULL。
TRX_WAIT_STARTED    交易开始等待锁定的时间，如果 TRX_STATE是LOCK WAIT; 否则NULL。
TRX_MYSQL_THREAD_ID    MySQL线程ID，与show processlist中的ID值相对应
TRX_QUERY    事务正在执行的SQL语句

查看锁

select * from  INFORMATION_SCHEMA.INNODB_LOCKS;

LOCK_ID    一个唯一的锁ID号，内部为 InnoDB。
LOCK_TRX_ID    持有锁的交易的ID
LOCK_MODE    如何请求锁定。允许锁定模式描述符 S，X， IS，IX， GAP，AUTO_INC，和 UNKNOWN。锁定模式描述符可以组合使用以识别特定的锁定模式。
LOCK_TYPE    锁的类型
LOCK_TABLE    已锁定或包含锁定记录的表的名称
LOCK_INDEX    索引的名称，如果LOCK_TYPE是 RECORD; 否则NULL
LOCK_SPACE    锁定记录的表空间ID，如果 LOCK_TYPE是RECORD; 否则NULL
LOCK_PAGE    锁定记录的页码，如果 LOCK_TYPE是RECORD; 否则NULL。
LOCK_REC    页面内锁定记录的堆号，如果 LOCK_TYPE是RECORD; 否则NULL。
LOCK_DATA    与锁相关的数据（如果有）。如果 LOCK_TYPE是RECORD，是锁定的记录的主键值，否则NULL。此列包含锁定行中主键列的值，格式为有效的SQL字符串。如果没有主键，LOCK_DATA则是唯一的InnoDB内部行ID号。如果对键值或范围高于索引中的最大值的间隙锁定，则LOCK_DATA 报告supremum pseudo-record。当包含锁定记录的页面不在缓冲池中时（如果在保持锁定时将其分页到磁盘），InnoDB不从磁盘获取页面，以避免不必要的磁盘操作。相反， LOCK_DATA设置为 NULL。

查看锁等待

select * from  INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

REQUESTING_TRX_ID    请求（阻止）事务的ID。
REQUESTED_LOCK_ID    事务正在等待的锁的ID。
BLOCKING_TRX_ID    阻止事务的ID。
BLOCKING_LOCK_ID    由阻止另一个事务继续进行的事务所持有的锁的ID

2、数据库锁机制

InnoDB存储引擎的行锁是通过锁住主键索引实现的，而不是记录。这是理解数据库锁问题的关键。

InnoDB特殊的索引机制，数据库操作使用主键索引时，InnoDB会锁住主键索引；使用非主键索引时，InnoDB会先锁住非主键索引，再锁定主键索引。如果操作不通过索引条件检索数据，InnoDB 则对表中的所有记录加锁，实际效果就和表锁一样。

InnoDB存储引擎有3种行锁的算法，分别是：

Record Lock:

单个记录上的锁
Record Lock总是会锁住索引记录，如果InnoDB存储引擎表在建立的时候没有设置任何一个索引，那么这时InnoDB存储引擎会使用隐式的主键来进行锁定。

Gap Lock: 间隙锁，锁定一个范围，但不包括记录本上
解决Phantom Problem（幻象/幻读），利用这种锁技术，锁定的不是单个值，而是一个范围。

Next-Key Lock: Gap Lock+Record Lock，锁定一个范围，并且锁定记录本身

3、乐观锁:

乐观锁不是锁，通过版本号判断数据是否更新

下单操作包括3步骤：
1.查询出商品信息
select (status,status,version) from t_goods where id=#{id}
2.根据商品信息生成订单
3.修改商品status为2
update t_goods 
set status=2,version=version+1
where id=#{id} and version=#{version};

4、全局锁

全局锁是对整个数据库实例加锁。典型使用场景是，做全库逻辑备份。只允许读的操作。写的操作会被阻塞。/5、表锁：

MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁

5、行锁：

事务A--begin;
update t set k=k+1 where id=1
update t set k=k+1 where id=2

事务B
事务B--begin;
update t set k=k+2 where id=1

事务A--commit

事务 B 的 update 语句会被阻塞，直到事务 A 执行 commit 之后，事务 B 才能继续执行。

事务 A 持有的两个记录的行锁，都是在 commit 的时候才释放的。

在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

6、行锁在开发中的技巧：

一个事务中两台update 一条insert.合理安排语句执行顺序，可提高并发度。

从顾客 A 账户余额中扣除电影票价；
给影院 B 的账户余额增加这张电影票价；
记录一条交易日志。

 

另外一个顾客 C 要在影院 B 买票
给影院 B 的账户余额增加这张电影票价；

那么这两个事务冲突的部分就是语句 2 了。因为它们要更新同一个影院账户的余额，需要修改同一行数据。

开发中如果事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁的申请时机尽量往后放那么影院账户余额这一行的锁时间就最少。这就最大程度地减少了事务之间的锁等待，提升了并发度。但是调整语句顺序，并不能完全避免死锁问题。

7、死锁：

事务 A 在等待事务 B 释放 id=2 的行锁，而事务 B 在等待事务 A 释放 id=1 的行锁。 事务 A 和事务 B 在互相等待对方的资源释放，就是进入了死锁状态：

事务A	事务B
begin	begin
update t set k=k+1 where id=1	/
/	update t set k=k+1 where id=2
update t set k=k+1 where id=2	/
/	update t set k=k+1 where id=1

 

8、死锁的两种解决策略：

1、设置超时时间：

一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。 innodb_lock_wait_timeout设置太长，服务等待时间不允许。 设置太短，出现死锁的时候，很快就可以解开，但如果不是死锁，而是简单的锁等待，就会出现误伤

2、主动发起死锁检测

另一种策略是，将参数 innodb_deadlock_detect 设置为 on，主动发起死锁检测。发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。

每当一个事务被锁的时候，就要看看它所依赖的线程有没有被别人锁住，如此循环，最后判断是否出现了循环等待（死锁）。时间复杂度为O（n）,假设1000 个并发线程要同时更新同一行，那么死锁检测操作就是 100 万这个量级的消耗CPU资源。

降低死锁检测成本策略：

（1）修改Mysql源码：对于相同行的更新，在进入引擎之前排队。这样在 InnoDB 内部就不会有大量的死锁检测工作了
（2）业务代码控制并发度：利用中间件，控制并发数据。如同一行同时最多只有 10 个线程在更新，那么死锁检测的成本降得很低。