mysql锁总结

参考文章

MySQL-死锁查询

事务与锁详解2

MySQL死锁系列-常见加锁场景分析

死锁的成因、场景以及死锁的避免

查询锁sql

1，查看当前的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

2，查看正在锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

3，查看等待锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

4，杀死进程id（就是上面命令的trx_mysql_thread_id列）

kill 661496

5、show open tables : 这条命令能够查看当前有那些表是打开的。In_use列表示有多少线程正在使用某张表，Name_locked表示表名是否被锁。

show open tables where in_use > 0;

这一般发生在Drop或Rename命令操作这张表时。故这条命令不能帮助解答我们常见的问题：当前某张表是否有死锁，谁拥有表上的这个锁等。

6、show processlist 显示哪些线程正在运行。status 显示状态。

show processlist

status	含义
locked	被其他查询锁住了。
User Lock	正在等待GET_LOCK()。
Killed	发送了一个kill请求给某线程，那么这个线程将会检查kill标志位，同时会放弃下一个kill请求。
Sending data	在处理SELECT查询的记录，同时正在把结果发送给客户端。
Sorting for group	正在为GROUP BY做排序。
Sorting for order	正在为ORDER BY做排序。
Removing duplicates	在执行一个SELECT DISTINCT方式的查询
Sleeping	正在等待客户端发送新请求。
Updating	正在搜索匹配的记录，并且修改它们。

更详细的status 请看 MySQL-死锁查询

7.杀死进程id（就是上面命令的id列）

kill 5

锁的类型

锁机制

共享锁（读锁）：其他事务可以读，但不能写。

排他锁（写锁） ：其他事务不能读取，也不能写。另外，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁。

意向共享锁（IS）：事务打算给数据行加行共享锁。事务在给一个数据行加共享锁前必须先取得该表的IS锁。
意向排他锁（IX）：事务打算给数据行加行排他锁。事务在给一个数据行加排他锁前必须先取得该表的IX锁。

细粒度

行锁：对行锁定。开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

表锁：对整个表锁定。开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

锁算法

现在user表只要id=1,3,7, 10,13 有记录，如下：

记录锁 （Record Locks）：锁定具体的记录

update user set name='张三'  where id=1

间隙锁（Gap Locks）：锁定范围，主要是阻塞插入

1，where 是范围时，给一定范围的记录加锁。

select * from user where id>4 and id<7 for update

锁定范围 ：id>4 and id<7

2，没有找到记录，给相邻的2记录范围的加锁。

update user set name='张三'  where id=6

锁定范围：id>4 and id<7

临键锁（Next-key Locks）: 锁定范围加记录

select * from user where id>5 and id<9 for update

锁定范围：id>=4 and id<7 + id>=7 and id<10

事务与锁

数据库如果事务设置不合理，容易出现 脏读，不可重复读，幻读。

脏读：事务A读取事务B正在修改且还没有提交的数据，如果事务B回滚，那事务A获取的数据就是错误的数据（脏数据）。

不可重复读：在事务中多次读取的结果不一样。如下：

步骤1：在事务A中第一次获取小明的分数90，

步骤2：恰巧事务B修改小明的分数为92并提交，

事务3，在事务A中需要再次读取小明的分数，结果是92

在同一个事务中，步骤1和步骤2的分数是不一样，这就是不重复读。

幻读：在事务中多次读取的记录数量不对。原因：在1次读取和第N次读取的期间中，有其他事务删除或新增了记录，

造成第N次的记录变多或变少。

事务的四种隔离级别

Read Uncommitted（未提交读，简称 RU） ：事务未提交的数据对其他事务也是可见的，会出现脏读，不可重复读，幻读问题。

Read Committed（已提交读，简称 RC） ：一个事务开始之后，只能看到已提交的事务所做的修改。解决脏读问题，会出现不可重复读 。

Repeatable Read（可重复读，简称 RR）：在同一个事务中多次读取同样的数据结果是一样的，解决不可重复读，会出现幻读 。

Serializable（串行化） ： 最高的隔离级别，通过强制事务的串行执行，解决所有问题。

事务隔离级别	脏读	不可重复读	幻读
未提交读	可能	可能	可能
已提交读	不可能	可能	可能
可重复读	不可能	不可能	InnoDB不可能，其他可能
串行化	不可能	不可能	不可能

快照读，当前读

快照读：读取的是记录数据的可见版本（可能是过期的数据），不用加锁。

当前读：读取的是记录数据的最新版本，并且当前读返回的记录都会加上锁（共享锁/排他锁），保证其他事务不会再并发的修改这条记录。

select ：RC 总是读取记录的最新版本，而 RR 是读取该记录事务开始时的那个版本，虽然这两种读取的版本不同，但是都是快照数据，并不会被写操作阻塞，所以这种 读操作称为 快照读（Snapshot Read）。

其余操作是当前读，如下：

SELECT ... LOCK IN SHARE MODE：加共享(S)锁
SELECT ... FOR UPDATE：加排他(X)锁
INSERT / UPDATE / DELETE：加排他(X)锁

innodb默认隔离级别是RR。事务与锁的使用场景可以看MySQL死锁系列-常见加锁场景分析

死锁场景

死锁原因：

当前线程拥有其他线程需要的资源

当前线程等待其他线程已拥有的资源

都不放弃自己拥有的资源

死锁场景：

1，顺序死锁：加锁顺序不一致容易出现的死锁。如下：

商品进货

transaction begin
# 进货 goods.id=5 加排他锁
update goods set inventory=inventory + 10 where id=5 ;
# 扣钱 account.id=10 加排他锁
update account set inventory=inventory - 80 where id=10
transaction end

商品退货

transaction begin
# 赔钱 account.id=10 加排他锁
update account set inventory=inventory - 80 where id=10
# 进货 goods.id=5 加排他锁
update goods set inventory=inventory + 10 where id=5 ;
transaction end

如果商品进货，退货同时发生则容易死锁。解决方法：把 goods ，account 加锁顺序改成一致。

2，共享锁，排它锁死锁

transaction begin

select * from goods where id=5 lock in share mode ;

sleep(10)

update goods set inventory=inventory + 10 where id=5 ;

transaction end

上面伪代码并发时，容易死锁。

主动加锁

SELECT ... LOCK IN SHARE MODE：加共享(S)锁

SELECT ... FOR UPDATE：加排他(X)锁

INSERT / UPDATE / DELETE：加排他(X)锁