@zero1036
2019-03-25T18:41:35.000000Z
字数 4640
阅读 1329
Mysql
首先,最重要是事务用来管理 insert,update,delete 语句
1、用 BEGIN, ROLLBACK, COMMIT来实现
2、直接用 SET 来改变 MySQL 的自动提交模式:
注意事项:
rollbackFor
需要捕获Exception
,否则如果抛出Exception
可能不会触发回滚
@Transactional(propagation = Propagation.REQUIRES_NEW,
rollbackFor = Exception.class, isolation = Isolation.READ_COMMITTED)
public void fn(){
var ok = mapper.update();
if(!ok){
//显式回滚
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
}
}
显式回滚语句:TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
事务隔离分为不同级别,包括读未提交(Read uncommitted)、读提交(read committed)、可重复读(repeatable read)(mysql默认级别)和串行化(Serializable)。
你可以用下列语句查询全局和会话事务隔离级别:
SELECT @@global.tx_isolation;
SELECT @@session.tx_isolation;
SELECT @@tx_isolation;
设置事务隔离级别:
set session transaction isolation level read uncommitted;
set session transaction isolation level read committed;
set session transaction isolation level repeatable;
set session transaction isolation level serializable;
锁类型 | 描述 | 粒度 |
---|---|---|
共享锁(S Lock) | 允许事务读一行数据 | 行锁 |
排他锁(X Lock) | 允许事务删除或更新一行数据 | 行锁 |
意向共享锁(IS Lock) | 事务想要获得一张表中某几行的共享锁 | 表锁 |
意向排他锁(IX Lock) | 事务想要获得一张表中某几行的排他锁 | 表锁 |
兼容性:列为已有所,行为期望获得锁
。 | IS | IX | S | X |
---|---|---|---|---|
IS | 兼容 | 兼容 | 兼容 | 不兼容 |
IX | 不兼容 | 兼容 | 不兼容 | 不兼容 |
S | 兼容 | 不兼容 | 兼容 | 不兼容 |
X | 不兼容 | 不兼容 | 不兼容 | 不兼容 |
命令show engine innodb status
查看当前锁情况
Innodb 1.0版本以后,建议通过information_schema架构下的表查询事务与锁的状态:information_schema innodb详解
如果一个事务T1已经获取的行r的共享锁,那么另外的事务T2可以立即获得行r的共享锁,因为读取并没有改变行r的数据,称为锁兼容。但若其他事务T3想获得行r的排他锁,则必须等待T1、T2释放行上的共享锁。
select
语句的上锁方式:
语句 | 锁类型 | amount |
---|---|---|
select | 没有上锁 | |
select ... for update | 排他锁(X LOCK) | Mysql会对查询结果中的每行都加排他锁,当没有其他线程对查询结果集中的任何一行使用排他锁时,可以成功申请排他锁,否则会被阻塞。行级锁都是基于索引的,如果一条SQL语句用不到索引是不会使用行级锁的,会使用表级锁。行级锁的缺点是:由于需要请求大量的锁资源,所以速度慢,内存消耗大! |
select ... lock in share mode; | 共享锁(S LOCK) | Mysql会对查询结果中的每行都加共享锁,当没有其他线程对查询结果集中的任何一行使用排他锁时,可以成功申请共享锁,否则会被阻塞。其他线程也可以读取使用了共享锁的表,而且这些线程读取的是同一个版本的数据。 |
意向锁:若将上锁的对象看成一棵树,那么对最下层的对象(粒度最小的行锁)上锁,首先需要对粗粒度的对象上锁。形成一个从上至下的上锁流程。设计目的是为了在一个事务中揭示下一行将被请求的锁类型。
如果需要对页上的记录r上X锁,那么分别需要对数据库A、表、页上意向锁IX,最后对记录r上X锁。如果任何一个部分导致等待,那么该操作需要等待粗粒度锁的完成。
A会话 | B会话 |
---|---|
对表T的行r上S锁 | |
计划对行r上X锁 | |
先对表T上IX锁 | |
IX与S不兼容,阻塞 |
1、查询条件命中唯一索引,NextKey Lock会自动降级为Record Lock
A会话 | B会话 |
---|---|
select * from account where id = 5 for update | |
insert into account (id, name ,amount) values (4,'tgtg',300); 不会阻塞 |
在会话A中,首先对id=5进行X锁定,由于id是主键且唯一,因此锁定的仅是5这个值,而不是(2, 5)这个范围;
2、
一致性的非锁定读是指InnoDB存储引擎通过行多版本控制(multi versioning)的方式来读取当前执行时间数据库中的行数据。如果读取的行正在执行Delete或Update操作,这是读取操作不会因此去等待行上锁的释放。相反,InnoDB存储引擎会去读取行的一个快照数据。
快照数据本身是没有额外的开销。同时,读取快照数据是不需要上锁的,因为没有事务需要对历史数据进行修改操作。
隔离级别Read Commited和Repeatable read下,InnoDB存储引擎使用非锁定的一致性读,但读取的快照数据不同。
repeatable read示例
A会话 | B会话 |
---|---|
begin; | |
select * from account; ## amount = 100 | |
begin; | |
update account set amount = 200 where amount = 100; | |
commit; | |
select * from account; ## amount = 100 | |
commit; |
read commited示例
A会话 | B会话 |
---|---|
begin; | |
select * from account; ## amount = 100 | |
begin; | |
update account set amount = 200 where amount = 100; | |
commit; | |
select * from account; ## amount = 200 | |
commit; |
用户需要显示地对数据库读取操作进行加锁以保证数据逻辑的一致性。要求数据库支持加锁语句。
select语句支持两种一致性锁定读(locking reading):
select ... for update;
select ... lock in share mode;
insert语句支持:
insert ... xxx select xxxx;
locking reading两者区别:
语句 | 锁类型 | amount |
---|---|---|
select ... for update | 排他锁(X LOCK) | Mysql会对查询结果中的每行都加排他锁,当没有其他线程对查询结果集中的任何一行使用排他锁时,可以成功申请排他锁,否则会被阻塞。行级锁都是基于索引的,如果一条SQL语句用不到索引是不会使用行级锁的,会使用表级锁。行级锁的缺点是:由于需要请求大量的锁资源,所以速度慢,内存消耗大! |
select ... lock in share mode; | 共享锁(S LOCK) | Mysql会对查询结果中的每行都加共享锁,当没有其他线程对查询结果集中的任何一行使用排他锁时,可以成功申请共享锁,否则会被阻塞。其他线程也可以读取使用了共享锁的表,而且这些线程读取的是同一个版本的数据。 |
对于外键值的插入与更新,首先需要查询父表的select操作,要求使用一致性锁定读。
如下例子,有主表account及其子表account_detail
account
id | name | amount |
---|---|---|
1 | tg | 600.00 |
2 | mark | 200.00 |
account_detail
id | account_id |
---|---|
1 | 1 |
会话执行:
A会话 | B会话 |
---|---|
begin; | |
delete from account where id = 2; | |
begin; | |
insert account_detail (account_id) (select id from account where id = 2); | |
堵塞 |
根据以上例子,B会话执行insert时会堵塞,因为会话A获得X锁,insert select为一致性读,无法获取与X不兼容的S锁。设想,如果外表的插入结果,是通过一致性非锁定读的读取结果,会出现数据不一致的情况。
A会话 | B会话 |
---|---|
begin; | |
delete from account where id = 2; | |
begin; | |
select id = @accountId from account where id = 2; | |
insert account_detail (account_id) value (@accountId); | |
commit; | |
commit; |
结果:account表中,id = 2的记录已被删除,但子表account_id = 2的记录却并被插入。