MySQL事务与锁相关记录

Jafeiyn

已于 2022-09-07 19:33:28 修改

阅读量276

点赞数

文章标签： mysql 数据库 java

于 2022-09-07 19:33:06 首次发布

本文链接：https://blog.csdn.net/qq_44528357/article/details/126751825

版权

MySQL事务四大特性：ACID特性

1.原子性（操作）
2.一致性（数据）
3.隔离性
4.持久性

MySQL并发事务问题：

1.脏写(更新丢失)-两个或多个事务更新操作同一条记录，基于最初选定的值更新该行，由于每个事务都不知道其他事务的存在，最后事务的更新覆盖了其他事务所做的更新操作
2.脏读-事务A读取到事务B已经修改但尚未提交的数据。若还在这个数据基础上做了操作，此时事务B回滚，事务A读取的数据无效，不符合一致性。（脏读读取到其他事务未提交的数据）
3.不可重复读-事务A内部的相同查询语句在不同时刻读出的结果不一致，不符合隔离性（不可重复读读取到的是其他事物已提交的更新数据）
4.幻读-事务A读到了事务B已提交的新增数据，不符合隔离性

MySQL隔离级别：（级别由低到高排序）

1.读未提交：不能彻底解决脏读、不可重复读、幻读
2.读已提交：解决脏读，不能彻底解决不可重复读、幻读（通过MVCC机制保证多事务并发读写操作性能，通过undo log版本链与事务每次select生成的一致性视图readview保证事务每次查到的结果是数据库最新已提交的数据从而解决脏读问题）
3.可重复读：解决脏读、不可重复读，不能彻底解决幻读
可重复读级别下在保证隔离性情况下执行查询操作不会加锁，更新操作会加锁（通过MVCC机制保证多事务并发读写操作性能，通过undo log版本链与事务第一次执行select生成的一致性视图readview保证事务每次查到的结果都和第一次查到的结果一致从而解决不可重复读问题）
4.可串行化：解决脏读、不可重复读、幻读
可串行化级别下执行增删改查操作都会加锁，其他事务执行任何CRUD操作都会阻塞等待

设置当前session会话的事务隔离级别：
set tx_isolation = ‘read-uncommitted’ / ‘read-committed’ / ‘repeatable-read’ / ‘serializable’;
或
set session transaction isolation level read UNCOMMITTED / read COMMITTED / repeatable read / serializable;

查看全局事务隔离级别：
show global variables like ‘tx_isolation’;

查看当前session会话的事务隔离级别：
show variables like ‘tx_isolation’;
或
select @@session.tx_isolation;

MySQL锁：

MySQL加锁是加在索引上的，无索引的行可能会升级为表锁
一、表锁：每次操作锁住整张表。开销小，加锁快，不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；一般用在整张表数据迁移场景；
加锁：lock table 表名 read/write; (注：多个表加锁中间用逗号隔开)
解锁：unlock tables;
查看表锁：show open tables; (注：In_use字段为1表示加了表锁，为0表示未加表锁)

二、行锁：每次操作锁住一行数据。开销打，加锁慢，会出现死锁；锁定粒度小，发生锁冲突的概率最低，并发度最高；
Innodb在执行查询操作时由于MVCC机制不会加锁，但是增删改操作会加行锁；
Myisam在执行查询操作时会自动给涉及到的表加读锁，在执行增删改操作时会自动给涉及到的表加写锁；
总结：读锁会阻塞写但不会阻塞读；写锁会阻塞读和写；

三、间隙锁：锁的是两个值之间的间隙（间隙锁只在可重复读级别下生效，在可重复读隔离级别下的临键锁可以解决幻读问题）。MySQL默认隔离级别为可重复读，范围更新时会加间隙锁。
若此时数据库中的表记录有id为1,2,3,6,8,12
此时有四个间隙为id -> (3,6),(6,8),(8,12),(12,正无穷)
如：update tmp_schedule_job set job_status = 2 where id > 4 and id < 10;
上述更新语句会在id -> (3,12]之间加锁（注意锁记录不包含3但包含最后的12），即锁的是包含这个范围之内的所有行记录以及行记录所在的间隙，其他session无法在此区间内新增或修改数据

四、临键锁：行锁与间隙锁的组合。如上述加锁的整个区间(3,12]可以叫做临键锁。

查看事务与锁：

查看事务：select * from INFORMATION_SCHEMA.INNODB_TRX;
查看锁：select * from INFORMATION_SCHEMA.INNODB_LOCKS;
查看锁等待：select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
释放锁：kill trx_mysql_thread_id (该值可在事务表INNODB_TRX中查看，一般把加锁那个事务blocking_trx_id的线程kill掉即可)
查看近期死锁日志信息：show engine innodb status\G;