事务
SQL Server 中的事务与错误处理
Miriam Antona
Software Engineer
数据集：银行交易
customers
| customer_id | first_name | last_name | email | phone |
|-------------|------------|-----------|-----------------------|-----------|
| 1 | Dylan | Smith | [email protected] | 555888999 |
| 2 | John | Antona | [email protected] | 555111222 |
| 3 | Astrid | Harper | [email protected] | 555000999 |
| 4 | Angus | Brown | [email protected] | 555222012 |
| 5 | David | Elcano | [email protected] | 555602314 |
数据集：银行交易
accounts
| account_id | account_number | customer_id | current_balance |
|------------|----------------------|-------------|-----------------|
| 1 | 55555555551234567890 | 1 | 25000,00 |
| 2 | 55555555559876543210 | 1 | 200,00 |
| 3 | 55555555557070700707 | 2 | 1000,00 |
| 4 | 55555555558080808080 | 2 | 90000,00 |
| 5 | 55555555559090909090 | 3 | 35000,00 |
数据集：银行交易
transactions
| transaction_id | account_id | amount | transaction_date |
|----------------|------------|----------|------------------------|
| 1 | 1 | -100,00 | 2019-03-18 19:12:36.81 |
| 2 | 2 | 100,00 | 2019-01-18 19:12:36.91 |
| 3 | 1 | -9000,00 | 2019-02-18 20:20:36.41 |
| 4 | 3 | 9000,00 | 2019-02-18 20:20:36.51 |
| 5 | 4 | -50,00 | 2019-02-20 08:02:06.20 |
什么是事务？
- 事务：由一条或多条语句组成，要么全部执行，要么全部不执行
什么是事务？
转账 $100：账户 A -> 账户 B
- 从账户 A 扣 $100
- 给账户 B 加 $100
操作 2 失败 -> 不能只从账户 A 扣 $100！
事务语句 - 开始事务（BEGIN）
BEGIN { TRAN | TRANSACTION }
[ { transaction_name | @tran_name_variable }
[ WITH MARK [ 'description' ] ]
]
[ ; ]
事务语句 - 提交事务（COMMIT）
COMMIT [ { TRAN | TRANSACTION } [ transaction_name | tran_name_variable] ]
[ WITH ( DELAYED_DURABILITY = { OFF | ON } ) ][ ; ]
事务语句 - 回滚事务（ROLLBACK）
ROLLBACK { TRAN | TRANSACTION }
[ transaction_name | @tran_name_variable |
savepoint_name | @savepoint_variable ] [ ; ]
事务 - 示例
- 账户 1 = $24,400
- 账户 5 = $35,300
BEGIN TRAN;
UPDATE accounts SET current_balance = current_balance - 100 WHERE account_id = 1;
INSERT INTO transactions VALUES (1, -100, GETDATE());
UPDATE accounts SET current_balance = current_balance + 100 WHERE account_id = 5;
INSERT INTO transactions VALUES (5, 100, GETDATE());
COMMIT TRAN;
事务 - 示例
- 账户 1 = $24,400
- 账户 5 = $35,300
| account_id | account_number | customer_id | current_balance |
|------------|----------------------|-------------|-----------------|
| 1 | 55555555551234567890 | 1 | 24300,00 |
| 5 | 55555555559090909090 | 3 | 35400,00 |
| transaction_id | account_id | amount | transaction_date |
|----------------|------------|---------|------------------------|
| 10 | 5 | 100,00 | 2019-06-07 18:26:27.46 |
| 19 | 1 | -100,00 | 2019-06-07 18:28:05.49 |
事务 - 示例
- 账户 1 = $24,400
- 账户 5 = $35,300
BEGIN TRAN;
UPDATE accounts SET current_balance = current_balance - 100 WHERE account_id = 1;
INSERT INTO transactions VALUES (1, -100, GETDATE());
UPDATE accounts SET current_balance = current_balance + 100 WHERE account_id = 5;
INSERT INTO transactions VALUES (5, 100, GETDATE());
ROLLBACK TRAN;
事务 - 示例
- 账户 1 = $24,400
- 账户 5 = $35,300
| account_id | account_number | customer_id | current_balance |
|------------|----------------------|-------------|-----------------|
| 1 | 55555555551234567890 | 1 | 24400,00 |
| 5 | 55555555559090909090 | 3 | 35300,00 |
事务 - TRY...CATCH 示例
- 账户 1 = $24,400
- 账户 5 = $35,300
BEGIN TRY
BEGIN TRAN;
UPDATE accounts SET current_balance = current_balance - 100 WHERE account_id = 1;
INSERT INTO transactions VALUES (1, -100, GETDATE());
UPDATE accounts SET current_balance = current_balance + 100 WHERE account_id = 5;
INSERT INTO transactions VALUES (5, 100, GETDATE());
COMMIT TRAN;
END TRY
BEGIN CATCH
ROLLBACK TRAN;
END CATCH
事务 - TRY...CATCH 示例
- 账户 1 = $24,400
- 账户 5 = $35,300
| account_id | account_number | customer_id | current_balance |
|------------|----------------------|-------------|-----------------|
| 1 | 55555555551234567890 | 1 | 24300,00 |
| 5 | 55555555559090909090 | 3 | 35400,00 |
| transaction_id | account_id | amount | transaction_date |
|----------------|------------|---------|------------------------|
| 10 | 5 | 100,00 | 2019-06-07 18:26:27.46 |
| 19 | 1 | -100,00 | 2019-06-07 18:28:05.49 |
事务 - TRY...CATCH 示例
- 账户 1 = $24,400
- 账户 5 = $35,300
BEGIN TRY
BEGIN TRAN;
UPDATE accounts SET current_balance = current_balance - 100 WHERE account_id = 1;
INSERT INTO transactions VALUES (1, -100, GETDATE());
UPDATE accounts SET current_balance = current_balance + 100 WHERE account_id = 5;
INSERT INTO transactions VALUES (500, 100, GETDATE()); -- ERROR!
COMMIT TRAN;
END TRY
BEGIN CATCH
ROLLBACK TRAN;
END CATCH
事务 - TRY...CATCH 示例
- 账户 1 = $24,400
- 账户 5 = $35,300
| account_id | account_number | customer_id | current_balance |
|------------|----------------------|-------------|-----------------|
| 1 | 55555555551234567890 | 1 | 24400,00 |
| 5 | 55555555559090909090 | 3 | 35300,00 |
事务 - 未显式使用事务
- 账户 1 = $24,400
- 账户 5 = $35,300
UPDATE accounts SET current_balance = current_balance - 100 WHERE account_id = 1;
INSERT INTO transactions VALUES (1, -100, GETDATE());
UPDATE accounts SET current_balance = current_balance + 100 WHERE account_id = 5;
INSERT INTO transactions VALUES (500, 100, GETDATE()); -- ERROR!
事务 - 未显式使用事务
- 账户 1 = $24,400
- 账户 5 = $35,300
| account_id | account_number | customer_id | current_balance |
|------------|----------------------|-------------|-----------------|
| 1 | 55555555551234567890 | 1 | 24300,00 |
| 5 | 55555555559090909090 | 3 | 35400,00 |
| transaction_id | account_id | amount | transaction_date |
|----------------|------------|---------|------------------------|
| 10 | 5 | 100,00 | 2019-06-07 18:26:27.46 |
Vamos praticar!
SQL Server 中的事务与错误处理
