Transakce
Transakce a zpracování chyb v SQL Serveru
Miriam Antona
Software Engineer
Datová sada: bankovní transakce
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 |
Datová sada: bankovní transakce
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 |
Datová sada: bankovní transakce
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 |
Co je transakce?
- Transakce: jeden nebo více příkazů, které jsou provedeny buď všechny, nebo žádný
Co je transakce?
Převod 100 $ z účtu A na účet B
- Odečíst 100 $ z účtu A
- Přidat 100 $ na účet B
Operace 2 SELŽE -> nelze odečíst 100 $ z účtu A!
Příkazy transakce – BEGIN
BEGIN { TRAN | TRANSACTION }
[ { transaction_name | @tran_name_variable }
[ WITH MARK [ 'description' ] ]
]
[ ; ]
Příkazy transakce – COMMIT
COMMIT [ { TRAN | TRANSACTION } [ transaction_name | tran_name_variable] ]
[ WITH ( DELAYED_DURABILITY = { OFF | ON } ) ][ ; ]
Příkazy transakce – ROLLBACK
ROLLBACK { TRAN | TRANSACTION }
[ transaction_name | @tran_name_variable |
savepoint_name | @savepoint_variable ] [ ; ]
Transakce – příklad
- Účet 1 = 24 400 $
- Účet 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;
Transakce – příklad
- Účet 1 = 24 400 $
- Účet 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 |
Transakce – příklad
- Účet 1 = 24 400 $
- Účet 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;
Transakce – příklad
- Účet 1 = 24 400 $
- Účet 5 = 35 300 $
| account_id | account_number | customer_id | current_balance |
|------------|----------------------|-------------|-----------------|
| 1 | 55555555551234567890 | 1 | 24400,00 |
| 5 | 55555555559090909090 | 3 | 35300,00 |
Transakce – příklad s TRY...CATCH
- Účet 1 = 24 400 $
- Účet 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
Transakce – příklad s TRY...CATCH
- Účet 1 = 24 400 $
- Účet 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 |
Transakce – příklad s TRY...CATCH
- Účet 1 = 24 400 $
- Účet 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
Transakce – příklad s TRY...CATCH
- Účet 1 = 24 400 $
- Účet 5 = 35 300 $
| account_id | account_number | customer_id | current_balance |
|------------|----------------------|-------------|-----------------|
| 1 | 55555555551234567890 | 1 | 24400,00 |
| 5 | 55555555559090909090 | 3 | 35300,00 |
Transakce – bez explicitní transakce
- Účet 1 = 24 400 $
- Účet 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!
Transakce – bez explicitní transakce
- Účet 1 = 24 400 $
- Účet 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 |
Lass uns üben!
Transakce a zpracování chyb v SQL Serveru
