Mysql 锁

锁

从锁的性能有乐观锁和悲观锁；锁的粒度有行锁、页锁、表锁；锁的对数据库操作类型有读锁、写锁、意向锁

• 乐观锁：采用cas机制，不会阻塞数据库操作，只会针对当前事务进行失败重试。(用于写操作不多的情况)
• 悲观锁：锁住操作的数据，阻塞其他事务对同一资源的操作。
• 行锁：锁住一行数据
  InnoDB中，行锁是加载索引上的，
  1.在RR隔离级别中，加锁过程中没有找到索引则会升级为表锁
  2.在RC隔离级别中，加锁过程中没有找到索引则不会有锁升级
• 表锁：锁住整张表，

 ‐‐手动增加表锁
lock table 表名称 read(write),表名称2 read(write);
‐‐查看表上加过的锁
show open tables;
 ‐‐删除表锁
unlock tables;

• 页锁：只有BDB存储引擎支持，锁住索引页。

• 间隙锁(Gap Locak):间隙锁在RR隔离级别生效，会锁住索引件的间隙(比如用a进行锁)
  
  
  当拥有索引的a进行加锁，则(111-120)都会被锁住。

• 读锁：(共享锁、S锁(Shared))：select … lock in share mode;
  读锁是共享的，多个事务可以同时读取同一个资源，但不允许其他事务修改。

• 写锁：(排他锁、x锁(exclusive))：select … for update;
  写锁是排他的，会阻塞其他的写锁和读锁，update、delete、insert都会加写锁。

• 读锁和写锁是悲观锁

• 意向锁(Intention Lock)：又称I锁，为了提高表锁效率而增加。mysql进行表锁时会扫描表中是否存在读锁或者写锁，当有事务给表的数据行加了共享锁或排他锁，同时会给表设置一个标识，代表已经有行锁了，其他事务要想对表加表锁时，就不必逐行判断有没有行锁可能跟表锁冲突了，直接读这个标识就可以确定自己该不该加表锁。

关于锁的一些查询：

show status like 'innodb_row_lock%'

Innodb_row_lock_current_waits: 当前正在等待锁定的数量
Innodb_row_lock_time: 从系统启动到现在锁定总时间长度
Innodb_row_lock_time_avg: 每次等待所花平均时间
Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花时间
Innodb_row_lock_waits: 系统启动后到现在总共等待的次数

–查看当前事务
select * from INFORMATION_SCHEMA.INNODB_TRX;
–查看当前存在的锁，8.0之后需要换成这张表performance_schema.data_locks
select * from INFORMATION_SCHEMA.INNODB_LOCKS;
–查看锁等待8.0之后需要换成这张表performance_schema.data_lock_waits
select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

–释放锁，tx_mysql_thread_id 可以从INNODB_TRX中查看到
kill trx_mysql_thread_id
在这里插入图片描述

–查看锁等待信息
show engine innodb status;

INFORMATION_SCHEMA.INNODB_TRX：

trx_id 事务id
trx_state 事务状态
trx_mysql_thread_id 事务线程id
trx_idolation_level 事务隔离级别
trx_requested_lock_id 锁等待的id
trx_wait_started 锁等待开始时间
trx_query 锁等待的sql

INFORMATION_SCHEMA.INNODB_LOCKS：

lock_id 锁id，或者锁等待id
lock_trx_id 事务id
lock_mode 锁类型，X：排他锁
lock_type 锁类型，RECORD：行锁
lock_index 锁的索引类型
lock_table 操作的表

INFORMATION_SCHEMA.INNODB_LOCK_WAITS：

requesting_trx_id 事务id
request_lock_id 即将加的锁的id
blocking_trx_id 造成锁等待的事务的id
blocking_lock_id 造成锁等待的锁的id

show engine innodb status;

可以通过事务id在status中查看一些详细信息：

上图可以看到，事务310798和事务310797出现了死锁。

MVCC多版本并发控制机制

• mysql 查询数据时是查询undo日志中的数据内容
• 每条记录在undo日志中会多trx_id(事务id)和roll_pointer(回滚指针)两个字段
• undo日志记录会记录回滚指针和事务id，通过回滚指针组成版本链
• 事务id会不断递增创建，后创建的事务id一定比先创建的事务id大。
• 所有事务结束，会情况undo日志版本链

可重复读隔离级别下的MVCC：

一致性试图read-view：
当事务开启，执行查询sql时会生成当前事务的一致性试图read-view,该视图在事务提交前都不会变化。
read-view视图会记录 当前所有未提交事务和当前已经创建的最大事务id(max_id)。

RR隔离级别下，开启事务后，执行查询sql会生成read-view。当再次执行查询sql时，会在undo日志中进行查找，确认undo日志版本链中最新(最后一条)的记录的事务id：

1. 如果事务id大于max_id，则表明该事务对当前查询的事务来说是未来的事务，则对于当前事务来说，该事务的数据应该是不可见的。
2. 如果务id小于max_id且在read-view视图中，则表明该事务对于当前事务来说是未提交的事务，则也不可见。

在以上两中情况下，通过回滚指针找到上一条undo日志记录，继续判断事务id

3. 如果事务id小于max_id，且不在read-view视图中，则表明该事务相对于当前事务已经提交，则对当前事务是可见的。

对于删除的情况可以认为是update的特殊情况，会将版本链上最新的数据复制一份，然后将trx_id修改成删除操作的
trx_id，同时在该条记录的头信息（record header）里的（deleted_flag）标记位写上true，来表示当前记录已经被删除，
在查询时按照上面的规则查到对应的记录如果delete_flag标记位为true，意味着记录已被删除，则不返回数据。

读已提交的隔离级别下，每次查询都会生成新的read-view视图。