事务及其ACID属性
- 原子性(Atomicity):在同一个事务中的语句,要么同时生效要么同时失败
- 一致性:在事务开始和完成时,数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改,以保持数据的完整性。
- 隔离性(Isolation):事务与事务之间不能互相影响
- 持久性(Durable) :事务一旦提交,就要永久保存。
并发事务处理带来的问题
问题 |
描述 |
脏读 |
读到其他事务未提交的事务 |
脏写 |
两个事务同时写一行数据,后面的覆盖了前面的那条。 |
不可重复读 |
在事务中重复执行相同查询语句,得到的结果不一样 |
幻读 |
在事务中重复执行相同查询语句,会得到其他事务提交的记录 |
隔离级别
mysql默认是可重读读。如果spring不指定就是这个级别,spring事务有指定的话优先spring
隔离级别 |
脏读 |
不可重复读 |
幻读 |
读未提交(read-uncommitted) |
可 |
可 |
可 |
读已提交 |
否 |
可 |
可 |
可重复度(REPEATABLE-READ) |
否 |
否 |
可 |
串行化 |
否 |
否 |
否 |
设置隔离级别
5.7查看隔离级别
show variables like 'tx_isolation';
8.0查看隔离级别
SHOW VARIABLES LIKE 'transaction_isolation';
5.7设置隔离级别
set tx_isolation='REPEATABLE-READ';
8.8设计隔离级别
set transaction_isolation='REPEATABLE-READ';
锁
- 从性能上分为乐观锁(用版本对比来实现)和悲观锁
- 从对数据库操作的类型分,分为读锁和写锁(都属于悲观锁)
读锁(共享锁,S锁(Shared)):针对同一份数据,多个读操作可以同时进行而不会互相影响
写锁(排它锁,X锁(eXclusive)):当前写操作没有完成前,它会阻断其他写锁和读锁
- 从对数据操作的粒度分,分为表锁和行锁
总结:
MyISAM在执行查询语句SELECT前,会自动给涉及的所有表加读锁,在执行update、insert、delete操作会自动给涉及的表加写锁。
InnoDB在执行查询语句SELECT时(非串行隔离级别),不会加锁。但是update、insert、delete操作会加行锁。
简而言之,就是读锁会阻塞写,但是不会阻塞读。而写锁则会把读和写都阻塞。
表锁测试
#手动增加表锁 read 加读 write加写 逗号后可批量
lock table 表名称 read(write),表名称2 read(write);
#查看表上加过的锁
show open tables;
#删除表锁
unlock tables;
session1对表A加了读锁,那么在session1和其他session中都可读。如果在session1中写数据报错,其他session写数据会阻塞
session1对表A加了写锁,session1中可读可写。其他session对该表的所有操作被阻塞
行锁测试(仅限innodb)
for update 行X锁
lock in share mode 行S锁
#事务一
start TRANSACTION;
select * from account where id = 1 for UPDATE;
#事务二 不管加S锁还是X锁都会阻塞
select * from account where id = 1 for UPDATE;
select * from account where id = 1 for lock in share mode;
case2
#事务一
start TRANSACTION;
select * from account where id = 1 for lock in share mode;
#事务二 加X锁阻塞 加S锁可以正常查出数据
select * from account where id = 1 for UPDATE;
select * from account where id = 1 for lock in share mode;
case3
#事务一
start TRANSACTION;
select * from account where id = 1 for UPDATE;
#事务二 在除了串行化的隔离级别情况下直接select是不会加S锁,所以这样可以读取成功
select * from account where id = 1 ;
start TRANSACTION; 后记得commit。在除了串行化的隔离级别情况下,一般的select是不会加S锁的,除非手动for lock in share mode,在串行化隔离级别情况下,普通的select也会加S锁。因为这样可以防止幻读的情况
幻读测试
在可重复度的隔离级别下
1.首先开启一个事务进行查询
start TRANSACTION;
select * from account
2.再创建一共事务插入一条新数据
insert into account (id,name,balance) VALUES (4,'cyz',600);
3.事务一 再次查询
4.事务一 查不到插入的最新数据,但是这时执行update语句后再查询,发现竟然可以读取到事务,这就是幻读。
UPDATE account SET name = 'mm' where id = 4
可重复读和java代码之间的问题
1.开启一个事务一,执行查询。
2.开启事务二,修改mm 减去 100,并提交
3.事务一再次查询,mm依然是600,但是实际库中已经变成500
4.事务一执行修改语句,并不提交事务
UPDATE account SET balance = balance-100 where id = 4
5.在事务一中再次查询,发现结果是400并没有错误,并没有因为select是600而修改错数据。
6.证明在不可重复读的情况下,相同的select语句虽然每次查的都是一样,但是真实数据并不是和查出来的一样。有一个类似副本的东西(并不是副本而是mvcc机制),但是修改又可以正确处理。保证了最终一致性,做一个类似副本的东西只是为了帮助程序员可以清晰的开发代码,不然每次查的都不一样,无法很好的做判断。
注意:
# 可以正确跟新
UPDATE account SET balance = balance-100 where id = 4
java中
// 获得库存
Account account = accountMapper.getById(4);
// 各种业务操作
//设置库存
account.setBalance(400);
accountMapper.updateById(account);
在这种java代码中 先获取库存再set,如果这时库存改变了,并不能像sql语句一样保持最终一致性。
得使用java锁的机制来规避
# 错误 java代码最后执行的是这句
UPDATE account SET balance = 400 where id = 4
# 正确
UPDATE account SET balance = balance-100 where id = 4
间隙锁(Gap Lock) 和 临键锁(Next-key Locks)
间隙锁是在可重复读隔离级别下才会生效。
间隙锁可以在某些情况下解决幻读。
假如表
在事务一中执行
update account set name = 'zhuge' where id > 5 and id <18;
则其他事务没法在这个范围所包含的所有行记录(包括间隙行记录)以及行记录所在的间隙里插入或修改任何数据,即id在(4,19]区间都无法修改数据,注意最后那个19也是包含在内的。这个就是间隙锁
如果是
update account set name = 'zhuge' where id > 5 and id <31;
31大于了表里最大的记录。这时候锁的区间就是 (4,正无穷 )
临键锁(Next-key Locks) 就是行锁与间隙锁的组合。像上面那个例子里的这个(3,20]的整个区间可以叫做临键锁
区别:间隙锁只是锁住间隙,并没有锁住中间具体的行。临建锁加上那些真实存在的行锁。
意向锁(表级别的锁)
意向锁有两种
- 意向共享锁(IS)表示事务意图在表中的单个行上设置共享锁。
- 意向排他锁(IX)表明事务意图在表中的单个行上设置独占锁。
在给表或者行加上S锁时 会给表加上IS锁
在给表或者行加上X锁时 会给表加上IX锁
作用:如果事务一给某一行加上了X锁。事务二要加表锁。如果没有意向锁的话就要一行行的判断有没有行锁,非常麻烦。如果在加上X锁时,再给表加上一个意向锁。其他事务只要看一下有没有意向锁就知道能不能加表锁了。IS锁类似。
意向锁解释
无索引行锁会升级为表锁
行锁都是加载索引上,如果没有索引就会升级成表锁
行锁分析
show status like 'innodb_row_lock%';
这里的锁定是指第二个事务想获得锁的时候阻塞的情况,如果只有一个事务没有人和他竞争锁不会统计
Innodb_row_lock_current_waits: 当前正在等待锁定的数量
Innodb_row_lock_time: 从系统启动到现在锁定总时间长度
Innodb_row_lock_time_avg: 每次等待所花平均时间
Innodb_row_lock_time_max:从系统启动到现在等待最长的一次所花时间
Innodb_row_lock_waits:系统启动后到现在总共等待的次数
对于这5个状态变量,比较重要的主要是:
Innodb_row_lock_time_avg (等待平均时长)
Innodb_row_lock_waits (等待总次数)
Innodb_row_lock_time(等待总时长)
尤其是当等待次数很高,而且每次等待时长也不小的时候,我们就需要分析系统中为什么会有如此多的等待,
然后根据分析结果着手制定优化计划。
查看INFORMATION_SCHEMA系统库锁相关数据表
# 查看事务
select * from INFORMATION_SCHEMA.INNODB_TRX;
# 查看锁5.7
select * from INFORMATION_SCHEMA.INNODB_LOCKS;
# 查看锁8.0
select * from performance_schema.data_locks
# 查看锁等待5.7
select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
# 查看锁等待8.0
select * from performance_schema.data_lock_waits;
‐‐ 释放锁,trx_mysql_thread_id可以从INNODB_TRX表里查看到
kill trx_mysql_thread_id
‐‐ 查看锁等待详细信息
show engine innodb status\G