数据库事务

1. 数据库事务特性

ACID四大特性：

原子性(Atomicity)：对数据修改操作要么全部执行，要么全不执行
一致性(Consistency)：事务执行前和执行后数据库都必须处于一致性状态
分离性(Isolation)：多个事务是相互隔离的
持久性(Durability)：一个事务提交，DBMS保证它对数据的改变是永久的

2. 锁模式/机制：共享锁、排他锁、更新锁、意向锁、架构锁、大容量更新、键范围

共享锁(S锁)：用于不更改或不更新数据的操作，只读数据

如一个事务对数据加上共享锁后，其他事务只能对此数据再加共享锁，不能加排他锁，只能读，不能修改。

排他锁(X锁)：用与数据修改操作，insert 、 update 、 delete ，确保不会同时同一资源进行多重更新

如一事务对数据加上排他锁后，其他事务不能再对此数据加共享或排他锁，获取排他锁的事务能读、修改数据。

更新锁(U锁)：可防止死锁，一个更新由一个事务组成，事务读取记录，获取数据一行或多行的共享锁，然后修改转为排他锁

如果两个事务获得了共享锁，试图更新数据，其中一个事务尝试将锁转换为排他锁，共享锁转换到排他锁必须等待一段时间，因为一个事务的排他锁与其他事务的共享锁不兼容，发生锁等待。第二个事务试图获取排他锁以进行更新，由于两个事务都要转为排他锁，并且每个事务都等待另一个事务释放共享锁，因此发生死锁。
使用更新锁解决这中情况。一次只有一个事务可以获得更新锁，如果事务修改资源，则更新锁转为排他锁。
一般情况下，更新直接获取独占锁，如果被更新的行已经存在独占锁，那么事务会尝试先获取共享锁，如果由多个会话再请求共享锁，当独占锁释放后就有多个会话获取共享锁，并尝试转换为排他锁

意向锁：意向共享、意向排他、意向排他共享、意向更新、共享意向更新、更新意向排他

防止其他事务以会使较低级别的锁无效的方式修改较高级别资源
提高数据库引擎再较高的粒度级别检测锁冲突的效率

3. 锁粒度：行级锁、表级锁、

行级锁

表级锁

4. 乐观锁、悲观锁

乐观锁：不是数据库自带的，需要自己实现

操作数据库时，认为此次操作不会导致冲突，直接修改数据，不加锁，进行更新后，再判断是否有冲突。
实现方法如：表中添加版本号 version 字段，每操作一次自增1，操作前获取 version，更新的时候带上条件 version 是否与库中的一致

1
2
3

select @version=version from 表 where id=@id;
...
update 表 set status=2, version=version+1 where id=@id and version=@version;

悲观锁：数据库已有，包含共享锁、排他锁

操作数据时，认为此次操作会出现数据冲突，每次操作时都要获取锁才能进行对相同数据库的操作。

5. 事务隔离级别(isolation level)

6个隔离级别(isolation level)。
隔离级别越高，请求锁越严格，一致性就越高，并发性越低，性能影响也越大。

READ UNCOMMITTED < READ COMMITTED < REPEATABLE READ < SERIALIZABLE

SQL Server默认的隔离级别是： read committed
MySQL默认的隔离级别是： repeatable read
Oracle默认的隔离级别是： read committed
PostgreSQL默认的隔离级别是： read committed

查看当前库的事务隔离级别(isolation level)：

DBCC USEROPTIONS

设置隔离级别：

SET TRANSACTION ISOLATION LEVEL <ISOLATION NAME>;

注意:在设置会话隔离时(REPEATABLE READ)两个单词需要用空格间隔开,但是在表隔离中可以粘在一起(REPEATABLEREAD)：

select * from 表名 with(repeatableread);

设置查询表隔离：

SELECT * FROM <TABLE> WITH (<ISOLATION NAME>);

1. READ UNCOMMITTED：

未提交读，读脏，相当于(NOLOCK)，默认的读操作：需要请求共享锁，允许其他事物读锁定的数据但不允许修改。
读操作不申请锁，允许读取未提交的修改(脏数据)，读操作不会影响写操作请求排他锁
SQL示例：

-- 创建表
create table orders (id int, price float);
-- 初始数据
insert into orders values(10,10), (11,11), (12,12), (13,13), (14,14);

-- 新建会话1，执行事务
begin transaction
    update orders set price=price+1 where id=10
    select * from orders where id=10

-- 新建会话2，执行查询，会一直等待中，即默认的 read commited
select * from orders where id=10

-- 新建会话3，执行查询，可成功查询，并且查询结果是 11
set transaction isolation level read uncommitted
select * from orders where id=10

-- 新建会话4，执行查询，可成功查询，并且查询结果是 11，with 可省略
select * from orders with(nolock) where id=10

-- 在执行事务的 会话1 中执行回滚，会话2 显示结果 10，会话3、会话4 再次查询结果为 10
rollback transaction

注意：若发生回滚，在更新数据和回滚之间，其他事务会获取到更新的数据，导致结果不一致

2. READ COMMITTED：

已提交读，默认级别，避免读取未提交的数据
读操作之前先申请获得共享锁，允许其他读操作读取该锁定的数据，但写操作必须等待锁释放，读取完会立刻释放共享锁
SQL示例：

-- 新建会话1，执行事务
begin transaction
    update orders set price=price+1 where id=10;
    select * from orders where id=10;

-- 新建会话2，执行查询，会一直等待中，即默认的 read commited
select * from orders where id=10

-- 新建会话3，执行查询，会一直等待，和会话2是一样的
set transaction isolation level read committed;
select * from orders where id=10;

-- 回到会话1，执行提交，会话2、会话3结果为11
commit transaction

注意：读操作完成会立即释放共享锁，也就是在读操作和更新操作之间，数据可以被修改，会导致结果不一致

3. REPEATABLE READ：

可以重复读，相当于(HOLDLOCK)
保证两个读操作之间，其他事务不能修改读取的数据，即读取数据完成后，不释放共享锁
SQL示例：

-- 新建会话1，执行事务，结果为 11
set transaction isolation level repeatable read
begin transaction
    select * from orders where id=11;

-- 新建会话2，结果 11
select * from orders where id=11;

-- 新建会话3，一直等待
update orders set price=price+1 where id=11;
select * from orders where id=11;

-- 回到会话1，查询并提交事务，结果为 11，会话3执行成功，结果变为 12
select * from orders where id=11;
commit

注意：读操作只会锁定查询条件的数据，即 id=11 的这条数据，其他事务可修改其他条件的数据，并可插入条件 id=11 的数据，导致事务中两次查询结果不一致，即不能锁定不存在表中的数据，称之为幻读

4. SERIALIZABLE：

可序列化，避免幻读问题
SQL示例：

-- 新建会话1，执行事务
set transaction isolation level serializable
begin transaction
    select * from orders where id=12;

-- 新建会话2，执行插入，会一直等待
insert into orders values(12, 13);
select * from orders where id=12;

-- 在会话1中，执行查询并提交事务，显示结果为一条数据 12，会话2执行结果为两条数据 12、13
select * from orders where id=12;
commit

5. SNAPSHOT：

6. READ COMMITTED SNAPSHOT：

快照
SNAPSHOT隔离级别和 serializable 类似，快照是事务之前的已提交版本，满足已提交读，可重复读，不幻读。
READ COMMITTED SNAPSHOT 与 read committed 类似，快照是读操作之前的已提交版本，保证已提交读，不保证可重复读，不能避免幻读，不需获取共享锁。
可以把事务已经提交的行的上一版本保存在TEMPDB数据库中，是基于行版本控制
在读操作不需要申请获得共享锁，所以即便数据已经存在排他锁也不影响读操作，任然可得到一致性，如果目前版本和预期版本不一致，读操作可以从 TEMPDB 中获取预期的版本
如果启用，delete 和 update 语句在做出修改前会把行的当前版本复制到 TEMPDB 中，而 insert 语句不需要再 TEMPDB 中进行版本控制，因为此时还没有行的旧数据
会对更新和删除操作产生性能的负面影响，但是有利于提高读操作的性能，因为读操作不需要获取共享锁。
当读取数据时可以保证操作读取的行是事务开始时可用的最后提交版本
该隔离级别实用的不是共享锁,而是行版本控制

在这个数据库下生成测试数据：

1 2	create table orders(id int, price float); insert into orders values(10,10), (11,11), (12,12), (13,13), (14,14);

SNAPSHOT 测试操作：

-- 首先需要在数据库级别上设置相关选项
alter database 数据库名 set allow_snapshot_isolation on;

-- 新建会话1，执行事务，返回结果 11
begin transaction
    update orders set price=price+1 where id=10;
    select * from orders where id=10;

-- 新建会话2，执行快照隔离级别，返回结果 10
set transaction isolation level snapshot
begin transaction
    select * from orders where id=10;

-- 在会话1中，执行提交事务
commit

-- 在会话2中，执行查询并提交事务，返回结果 10
select * from orders where id=10;
commit

-- 在会话1中，执行查询，结果为 11
select * from orders where id=10;

-- 在会话2中，执行查询，结果是 11
select * from orders where id=10;

注意：

隔离级别保证操作读取的行是事务开始时可用的最后已提交版本，由于会话1的事务未提交,所以订单10的最后提交版本还是修改前的价格10，所以会话2读取到的价格是会话2事务开始前的已提交版本价格10，当会话1提交事务后，会话2重新新建一个事务此时事务开启前的价格已经是11了，所以查询到的价格是11，同时SNAPSHOT隔离级别还能保证SERIALIZABLE的隔离级别
如果在会话1事务开始后，会话2中添加更新操作：

1	update orders set price=price+1 where id=10;

会话1提交事务后，会话2提交，则会报错。

READ COMMITTED SNAPSHOT 测试操作：

-- 修改数据库选项
alter database 数据库名 set read_committed_snapshot on;

-- 新建会话1，执行事务，结果是 12
begin transaction
    update orders set price=price+1 where id=11;
    select * from orders where id=11;

-- 新建会话2，执行查询，结果是 11
begin transaction
    select * from orders where id=11;

-- 在会话1中，提交事务
commit;

-- 在会话2中，再次执行查询并提交事务，结果是 12
select * from orders where id=11;
commit;

注意：会话2执行时，会话1还未提交，所以最后快照版本是11，会话2执行时，会话1已经提交，所以最后快照版是12

禁用快照版本：

1 2	alter database 数据库名 set allow_snapshot_isolation off; alter database 数据库名 set read_committed_snapshot off;