mysql innodb 的锁、事务、添加索引、死锁

最新推荐文章于 2022-04-01 15:58:04 发布

zwww7766

最新推荐文章于 2022-04-01 15:58:04 发布

阅读量201

点赞数

原文链接：https://blog.csdn.net/luzhensmart/article/details/81675527

版权

数据库：mysql 5.7

一些概念

sql 语句默认是事务，会自动提交。
默认情况下 select 不加锁用的非锁定读，update 会加写锁

事务隔离级别

读未提交: 事务未提交，但是修改结果可以被看到。
读提交: 事务未提交，修改结果不可以被看到。
可重复读: 事务在执行期间看到的数据前后必须一致。
串行化: 串行化执行事务，后面的事务必须等待前一个事务完毕

读锁、写锁、乐观锁

MySQL 并发控制 – 读锁、写锁、乐观锁

读锁(共享锁)：读取结果集的最新版本，同时防止其他事务产生更新该结果集，随提交释放
使用：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
写锁(排它锁，互斥锁)：即事务A 对某些记录添加写锁时，事务B 无法向这些记录添加写锁或者读锁（不添加锁的读取是可以的），事务B 也无法执行对锁住的数据 update、delete操作
使用：SELECT * FROM table_name WHERE ... FOR UPDATE
在使用读锁、写锁时都需要注意，读锁、写锁属于行级锁。即事务1 对商品A 获取写锁，和事务2 对商品B 获取写锁互相不会阻塞的。需要我们注意的是我们的SQL要合理使用索引，当我们的SQL 全表扫描的时候，行级锁会变成表锁。
使用EXPLAIN查看 SQL是否使用了索引，扫描了多少行.
乐观锁: 逻辑锁，通过版本号字段version. 数据更新时，对版本号+1，同时将提交数据的version 与数据库中对应记录的当前version 进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据.

update t_goods 
set status=2,version=version+1
where id=#{id} and version < #{version}; // 更新前将version自增

update t_goods 
set status=2,version=version+1
where id=#{id} and version = #{version}; // 更新前version 不自增

分析：表写锁，表读锁其实规矩是一样的，只不过范围不同。

表锁和行锁

表锁的情况
sub字段不是索引
事务A：

begin;
update b_settlement set `order`='124' where sub=3;

不提交,新开一个客户端,更改另一行。
事务B：

update b_settlement set `order`='123' where sub=2;

无反应
此时再对第事务A做提交，表锁解除，事务B可执行。更新成功

转换为行锁, id 字段为主键
事务A：

begin;
update b_settlement set `order`='124' where id=3;

不做提交
事务B：

update b_settlement set `order`='123' where id=2;

事务B不受影响说明无表锁

事务C:

update b_settlement set `channel`='123' where id=3;

事务C无法执行。因为事务A获取了行锁，
此时提交事务A，行锁释放，事务C执行完毕。

Mysql InnoDB引擎的行锁和表锁

记一个死锁状况

现有资源 c、事务A、事务B
事务A需对资源c做结算操作，在更改资源c时需要先读取资源c的当前数据, 所以A事务中有一个c资源的读锁即共享锁。
事务B需对资源c做冻结操作，需要获取一个资源c的写锁即独占锁。但需要等待事务A释放共享锁才行。此时事务A等待独占锁，事务B等待释放共享锁。发生了死锁

记录

两个事务中对同一个表insert 测试不会锁。空了再分析加锁逻辑。

极客时间课程记录

21 | 为什么我只改一行的语句，锁这么多？
我总结的加锁规则里面，包含了两个“原则”、两个“优化”和一个“bug”。
原则 1：加锁的基本单位是 next-key lock。希望你还记得，next-key lock 是前开后闭区间。
原则 2：查找过程中访问到的对象才会加锁。
优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

现场log

MySQL】如何阅读死锁日志

mysql> show engine innodb status \G; 
*************************** 1. row ***************************
  Type: InnoDB
  Name: 
Status: 
=====================================
2020-04-14 16:47:47 0x7f57f43f2700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 47 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 142143 srv_active, 0 srv_shutdown, 378107 srv_idle
srv_master_thread log flush and writes: 520212
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 337709
OS WAIT ARRAY INFO: signal count 1203451
RW-shared spins 0, rounds 2003714, OS waits 106136
RW-excl spins 0, rounds 5358211, OS waits 15123
RW-sx spins 2066, rounds 55128, OS waits 1249
Spin rounds per wait: 2003714.00 RW-shared, 5358211.00 RW-excl, 26.68 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2020-04-14 04:48:18 0x7f580337d700
*** (1) TRANSACTION:
TRANSACTION 2716987, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 1
MySQL thread id 2358, OS thread handle 140015734531840, query id 12729827 192.168.45.3 root updating
UPDATE `wallet` SET `amount` = `amount` - ?  WHERE (uuid = ?) AND (amount >= ?)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 38 page no 4 n bits 384 index uuid_amount of table `ta_finance`.`wallet` trx id 2716987 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 313 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 8; hex 801c96c431b92d01; asc     1 - ;;
 1: len 8; hex 800000000000c800; asc         ;;
 2: len 4; hex 800000d4; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 2716985, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
12 lock struct(s), heap size 1136, 20 row lock(s), undo log entries 9
MySQL thread id 2357, OS thread handle 140015987840768, query id 12729855 192.168.45.3 root Searching rows for update
UPDATE `wallet` SET `amount` = `amount` + ?  WHERE (uuid = ?)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 38 page no 4 n bits 384 index uuid_amount of table `ta_finance`.`wallet` trx id 2716985 lock_mode X locks gap before rec
Record lock, heap no 164 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 8; hex 801129811ab92d01; asc   )   - ;;
 1: len 8; hex 8000000000000000; asc         ;;
 2: len 4; hex 800000a7; asc     ;;

Record lock, heap no 272 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 8; hex 8011282b08b92d01; asc   (+  - ;;
 1: len 8; hex 8000000005fd4633; asc       F3;;
 2: len 4; hex 800000a6; asc     ;;

Record lock, heap no 305 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 8; hex 801c96a90db92d01; asc       - ;;
 1: len 8; hex 8000000027463777; asc     'F7w;;
 2: len 4; hex 800000d3; asc     ;;

Record lock, heap no 313 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 8; hex 801c96c431b92d01; asc     1 - ;;
 1: len 8; hex 800000000000c800; asc         ;;
 2: len 4; hex 800000d4; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 38 page no 4 n bits 384 index uuid_amount of table `ta_finance`.`wallet` trx id 2716985 lock_mode X waiting
Record lock, heap no 313 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 8; hex 801c96c431b92d01; asc     1 - ;;
 1: len 8; hex 800000000000c800; asc         ;;
 2: len 4; hex 800000d4; asc     ;;

*** WE ROLL BACK TRANSACTION (1)