MySQL 都有什么锁
MySQL 有三种锁的级别: 页级, 表级, 行级, 内存级(latch).
表级锁: 开销小, 加锁快; 不会出现死锁; 锁定粒度大, 发生锁冲突的概率最高, 并发度最低.
行级锁: 开销大, 加锁慢; 会出现死锁; 锁定粒度最小, 发生锁冲突的概率最低, 并发度也最高.
页面锁: 开销和加锁时间界于表锁和行锁之间; 会出现死锁; 锁定粒度界于表锁和行锁之间, 并发度一般
算法:
next KeyLocks 锁, 同时锁住记录(数据), 并且锁住记录前面的 Gap
Gap 锁, 不锁记录, 仅仅记录前面的 Gap
Recordlock 锁(锁数据, 不锁 Gap)
所以其实 Next-KeyLocks=Gap 锁 + Recordlock 锁
什么情况下会造成死锁
所谓死锁 DeadLock 是指两个或两个以上的进程在执行过程中,
因争夺资源而造成的一种互相等待的现象, 若无外力作用, 它们都将无法推进下去.
此时称系统处于死锁状态或系统产生了死锁, 这些永远在互相等竺的进程称为死锁进程.
表级锁不会产生死锁. 所以解决死锁主要还是针对于最常用的 InnoDB.
死锁的关键在于: 两个 (或以上) 的 Session 加锁的顺序不一致.
那么对应的解决死锁问题的关键就是: 让不同的 session 加锁有次序
4, 下面就简单来重现一下死锁:
死锁重现:
事务 A:
- root@test 16:01>select connection_id();
- +-----------------+
- | connection_id() |
- +-----------------+
- | 47274 |
- +-----------------+
- 1 row in set (0.01 sec)
- root@test 16:02>set autocommit =0;
- Query OK, 0 rows affected (0.00 sec)
- root@test 16:02>select * from t where id =1 for update;
- +----+
- | id |
- +----+
- | 1 |
- +----+
- 1 row in set (0.00 sec)
- root@test 16:02>select * from t where id =2 for update;
- ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
- root@test 16:03>
事务 B:
- root@test 16:02>select connection_id();
- +-----------------+
- | connection_id() |
- +-----------------+
- | 47272 |
- +-----------------+
- 1 row in set (0.00 sec)
- root@test 16:02>set autocommit =0;
- Query OK, 0 rows affected (0.00 sec)
- root@test 16:02>select * from t where id =2 for update;
- +----+
- | id |
- +----+
- | 2 |
- +----+
- 1 row in set (0.00 sec)
- root@test 16:03>select * from t where id =1 for update;
- +----+
- | id |
- +----+
- | 1 |
- +----+
- 1 row in set (5.53 sec)
- ===========================================
死锁信息:
- LATEST DETECTED DEADLOCK
- 2018-10-19 16:03:14 7f9612b6d700
- (1) TRANSACTION:
- TRANSACTION 870600, ACTIVE 11 sec starting index read
- MySQL tables in use 1, locked 1
- LOCK WAIT 3 lock struct(s), heap size 360, 2 row lock(s)
- MySQL thread id 47272, OS thread handle 0x7f9612e38700, query id 1112421 127.0.0.1 root statistics
- select from t where id =1 for update
- **(1) WAITING FOR THIS LOCK TO BE GRANTED:
- RECORD LOCKS space id 330 page no 3 n bits 72 index PRIMARY of table test.t trx id 870600 lock_mode X locks rec but not gap waiting (2) TRANSACTION:
- TRANSACTION 870599, ACTIVE 22 sec starting index read
- MySQL tables in use 1, locked 1
- 3 lock struct(s), heap size 360, 2 row lock(s)
- MySQL thread id 47274, OS thread handle 0x7f9612b6d700, query id 1112422 127.0.0.1 root statistics
- select * from t where id =2 for update
- (2) HOLDS THE LOCK(S):
- RECORD LOCKS space id 330 page no 3 n bits 72 index PRIMARY of table test.t trx id 870599 lock_mode X locks rec but not gap (2) WAITING FOR THIS LOCK TO BE GRANTED:
- RECORD LOCKS space id 330 page no 3 n bits 72 index PRIMARY of table test.t trx id 870599 lock_mode X locks rec but not gap waiting
- *** WE ROLL BACK TRANSACTION (2)
5 分析:
1, 这上面是显示是事务产生死锁的 sql 并打印出相应所持和等待的锁
2, 上面的信息并没有输出事务死锁之前的 sql, 所以可以直接堆出两个事务执行的 sql 使他们相互持有了对方等待的锁
3, 造成死锁是必然的, 慢 sql 和不合理的业务的逻辑是造成死锁过多的主要原因
重要的事情说三遍: 优化 sql, 优化业务, 优化逻辑
来源: http://www.bubuko.com/infodetail-2875602.html