参考文章
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)锁