SNAPSHOT

SQL Server 中的事务与错误处理

Miriam Antona

Software Engineer

SNAPSHOT

每次修改都会存入 tempDB 数据库
只能看到 SNAPSHOT 事务开始前已提交的更改和自身更改
看不到其他事务在 SNAPSHOT 开始后所做的任何更改
读不阻塞写，写不阻塞读
可能出现更新冲突

ALTER DATABASE myDatabaseName SET ALLOW_SNAPSHOT_ISOLATION ON;

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

SNAPSHOT - 隔离级别对比

	脏读	不可重复读	幻读
READ UNCOMMITTED	是	是	是
READ COMMITTED	否	是	是
REPEATABLE READ	否	否	是
SERIALIZABLE	否	否	否
SNAPSHOT	否	否	否

SNAPSHOT - 示例

事务1

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

BEGIN TRAN
    SELECT * FROM accounts;

|account_id|account_number      |...| current_balance|
|----------|--------------------|---|----------------|
|1         |55555555551234567890|...| 25000,00       |
|2         |55555555559876543210|...| 200,00         |
|...       |...                 |...| ...            |
|15        |55555555551234567890|...| 25000,00       |

事务2

...

BEGIN TRAN
    INSERT INTO accounts
    VALUES (11111111111111111111, 1, 25000);

    UPDATE accounts 
    SET current_balance = 30000 WHERE account_id = 1;

    SELECT * FROM accounts;
COMMIT TRAN

不会被阻塞！

SNAPSHOT - 示例

事务1

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

BEGIN TRAN
    SELECT * FROM accounts;

    SELECT * FROM accounts;

|account_id|account_number      |...| current_balance|
|----------|--------------------|---|----------------|
|1         |55555555551234567890|...| 25000,00       |
|2         |55555555559876543210|...| 200,00         |
|...       |...                 |...| ...            |
|15        |55555555551234567890|...| 25000,00       |

事务2

...

BEGIN TRAN
    INSERT INTO accounts
    VALUES (11111111111111111111, 1, 25000);

    UPDATE accounts 
    SET current_balance = 30000 WHERE account_id = 1;

    SELECT * FROM accounts;
COMMIT TRAN

不会被阻塞！

SNAPSHOT - 总结

优点：

良好的一致性：在不阻塞的情况下避免脏读、不可重复读和幻读

缺点：

tempDB 占用增大

何时使用：

需要强一致性且不希望产生阻塞时

READ COMMITTED SNAPSHOT

更改 READ COMMITTED 的行为

ALTER DATABASE myDatabaseName SET READ_COMMITTED_SNAPSHOT {ON|OFF};

默认 OFF
使用 ON：

ALTER DATABASE myDatabaseName SET ALLOW_SNAPSHOT_ISOLATION ON;

设为 ON 后，READ COMMITTED 只能看到该语句开始前已提交的更改
不会出现更新冲突

READ COMMITTED SNAPSHOT - 示例

事务1

 SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 BEGIN TRAN 
   UPDATE accounts
   SET current_balance = 30000
   WHERE account_id = 1;

COMMIT TRAN

事务2

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN

    SELECT current_balance FROM accounts 
    WHERE account_id = 1;

| current_balance |
|-----------------|
| 35000,00        |

READ COMMITTED SNAPSHOT - 示例

事务1

 SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 BEGIN TRAN 
   UPDATE accounts
   SET current_balance = 30000
   WHERE account_id = 1;

COMMIT TRAN

事务2

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN

    SELECT current_balance FROM accounts 
    WHERE account_id = 1;

    SELECT current_balance FROM accounts 
    WHERE account_id = 1;

| current_balance |
|-----------------|
| 30000,00        |

WITH (NOLOCK)

用于读取未提交数据
READ UNCOMMITTED 作用于整个连接，WITH (NOLOCK) 作用于指定表
在任意隔离级别下，若只想从特定表读取未提交数据可用

WITH (NOLOCK) - 示例

账户5原始余额 = $35,000

事务1

BEGIN TRAN
  UPDATE accounts
  SET current_balance = 30000
  WHERE account_id = 5;

事务2

...

SELECT current_balance
FROM accounts WITH (NOLOCK) 
WHERE account_id = 5;

| current_balance |
|-----------------|
| 30000,00        |

Ayo berlatih!

SQL Server 中的事务与错误处理