SQL 事务、锁

2021-11-30  本文已影响0人  李霖弢

事务（TRANSACTION）

事务是数据库操作的程序执行单元，保证成批的DML语句要么全执行，要么全不执行。如中间发现错误，会被回滚（Rollback）到事务开始前的状态。
MySQL常见的数据库引擎中支持事务的是InnoDB，不同数据库引擎原理和表现不同，以下内容皆基于InnoDB。

四大特性（ACID）

1）原子性 Atomicity：指整个数据库事务是不可分割的原子，要么都成功，要么都不成功。
2）一致性 Consistency：事务必须使数据库从一个一致性状态变换到另外一个一致性状态。
3）隔离性 Isolation：也可以叫做并发控制、可串行化、锁等，要求并发的事务间互不干扰。通过事务的隔离级别进行设置。
4）持久性 Duration：事务一旦提交，结果就应该永久保存下来。

事务提交
  1. 通过 ROLLBACK / COMMIT 显式提交
  2. 通过 DDL 或 DCL 语句隐式提交：ALTER，AUDIT，COMMENT，CONNECT，CREATE，DISCONNECT，DROP，
    EXIT，GRANT，NOAUDIT，QUIT，REVOKE，RENAME。
  3. 自动提交（默认开启）
    MVCC下，单行指令都是事务。如果关闭自动提交，则所有单行指令都需要commit之后才能持久化。
select @@autocommit; --查看开启状态
SET AUTOCOMMIT ON；
隔离级别

一个事务与其他事务隔离的程度称为隔离级别，InnoDB通过锁与MVCC共同实现隔离。

隔离级别 脏读 不可重复读 幻读
READ UNCOMMITTED（读未提交） 可能 可能 可能
READ COMMITTED（读提交） 不可能 可能 可能
REPEATABLE READ（可重复读） 不可能 不可能 可能
SERIALIZABLE（串行） 不可能 不可能 不可能
常用语法
begin;
update temp_user set mobile=111 where id =1;
rollback;

select @@tx_isolation;
select @@global.tx_isolation;
set session transaction isolation level read committed;
set global transaction isolation level repeatable read;
MVCC（multi version concurrent control 多版本并发控制）

InnoDB中根据MVCC，每个单独的操作也是一个事务。
每开启一个事务，事务版本号+1
每行记录都有两个隐藏列：创建版本号和删除版本号

REPEATABLE-READ模式下的快照读与当前读
select * from table where ?;

select * from table where ? lock in share mode;
select * from table where ? for update;
insert into table values (…); 
update table set ? where ?; 
delete from table where ?;
案例一：事务A等待事务B的锁
  1. 事务A
start transaction;
select * from user_token;--产生快照
  1. 事务B
start transaction;
insert into user_token VALUES(4,4,"4967e70c5d7e11ecb5c90242ac110003","pc","2022-02-02 22:22:22.222222");
--当前读，获取了共享锁
  1. 事务A
select * from user_token;--从快照读取，没有新数据
update user_token set user_id = 1 where id = 4;--当前读，暂无响应，等待B事务的锁释放
  1. 事务B
commit;--提交并释放锁
  1. 事务A
--上一步update操作获得锁，完成更新
select * from user_token;--此时快照也更新，获得了最新数据
commit;--提交并释放锁
案例二：手动加锁解决幻读
  1. 事务A
start transaction;
select * from user_token;--产生快照
  1. 事务B
start transaction;
insert into user_token VALUES(4,4,"4967e70c5d7e11ecb5c90242ac110003","pc","2022-02-02 22:22:22.222222");
commit;--提交并释放锁
  1. 事务A
select * from user_token;--从快照读取，没有新数据
update user_token set user_id = 1 where id = 4;--当前读，更新成功，快照也更新
select * from user_token;--从新快照读取，多了id=4的数据,出现幻读！
commit;--提交并释放锁

其实应在事务A初次select时即添加锁，则事务B会在A结束后才执行：

start transaction;
select * from user_token lock in share mode;--共享锁
--或
select * from user_token for update;--排他锁

锁

操作已上锁的数据时，数据库会自动等待锁释放。长时间不释放会超时报错：

Err] 1205 - Lock wait timeout exceeded; try restarting transaction

锁粒度

表级锁（MySQL Server实现）

开销小，加锁快；不会出现死锁；
锁定粒度大，发生锁冲突的概率高，并发度低。
当前会话结束时，表锁会自动unlock。

lock tables 表名 read; --表添加共享锁
lock tables 表名 write; --表添加排他锁
flush tables with read lock; --所有表添加共享锁
flush tables with write lock; --所有表添加排他锁
unlock tables; --解除所有表级锁
页级锁（仅BDB支持）

介于行级锁和表级锁之间

行级锁（InnoDB支持）

开销大，加锁慢；会出现死锁；
锁定粒度小，发生锁冲突的概率低，并发度高。

加锁时机

悲观锁

每次读取数据的时候都默认其他线程会更改数据，因此先锁定，再修改。这种方式称为悲观并发控制【Pessimistic Concurrency Control，“PCC”】，安全但效率较低。

乐观锁

乐观锁假设数据一般情况不会造成冲突，所以在数据进行提交更新时才进行锁定。乐观锁适用于读多写少的场景，效率较高。

锁的兼容性

共享锁【shared locks】

又称为读锁，S 锁。其他事务能访问到数据，但是只能读不能修改。
当前事务可以将其升级为排他锁。

排他锁【exclusive locks】

又称为写锁，X 锁，独占锁，互斥锁。如果一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁。

死锁

多个事务在执行过程中，因争夺资源而造成的一种相互等待的现象。若无外力作用，事务都将无法推进下去。
InnoDB 会自动检测死锁，使一个undo较少的事务释放锁回退，另一个事务获取锁。
频繁触发对性能影响较大，应尽量避免。可通过show engine innodb status查看死锁日志

表死锁

一个事务访问表A（锁住了表A），然后企图访问表B；另一个事务访问了表B（锁住了表B），然后企图访问表A；这时两个事务互相等待对方释放，就产生了死锁。

行死锁

类似表死锁，两行数据交叉先后访问也会造成行死锁。
此外如果一条 SQL 语句操作了非主键索引，MySQL 会先锁定该非主键索引，再锁定相关的主键索引。此时如果有另一个事务锁定了主键索引，则发生死锁。

上一篇下一篇

猜你喜欢

热点阅读