Транзакции

Транзакции и обработка ошибок в 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 |
Транзакции и обработка ошибок в SQL Server

Набор данных: банковские транзакции

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        |
Транзакции и обработка ошибок в SQL Server

Набор данных: банковские транзакции

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 |
Транзакции и обработка ошибок в SQL Server

Что такое транзакция?

  • Транзакция: одна или несколько инструкций, которые выполняются либо все вместе, либо ни одна
Транзакции и обработка ошибок в SQL Server

Что такое транзакция?

Перевод $100 со счёта A на счёт B

  1. Списать $100 со счёта A
  2. Зачислить $100 на счёт B

Операция 2 ЗАВЕРШАЕТСЯ ОШИБКОЙ -> Нельзя списать $100 со счёта A!

Транзакции и обработка ошибок в SQL Server

Операторы транзакций — BEGIN (начало)

BEGIN { TRAN | TRANSACTION }   
    [ { transaction_name | @tran_name_variable }  
      [ WITH MARK [ 'description' ] ]  
    ]  
[ ; ]
Транзакции и обработка ошибок в SQL Server

Операторы транзакций — COMMIT (фиксация)

COMMIT [ { TRAN | TRANSACTION } [ transaction_name | tran_name_variable] ] 
       [ WITH ( DELAYED_DURABILITY = { OFF | ON } ) ][ ; ]
Транзакции и обработка ошибок в SQL Server

Операторы транзакций — ROLLBACK (откат)

ROLLBACK { TRAN | TRANSACTION }   
     [ transaction_name | @tran_name_variable | 
       savepoint_name | @savepoint_variable ]   [ ; ]
Транзакции и обработка ошибок в SQL Server

Транзакция — пример

  • Счёт 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;
Транзакции и обработка ошибок в SQL Server

Транзакция — пример

  • Счёт 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 |
Транзакции и обработка ошибок в SQL Server

Транзакция — пример

  • Счёт 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;
Транзакции и обработка ошибок в SQL Server

Транзакция — пример

  • Счёт 1 = $24 400
  • Счёт 5 = $35 300
| account_id | account_number       | customer_id | current_balance |
|------------|----------------------|-------------|-----------------|
| 1          | 55555555551234567890 | 1           | 24400,00        |
| 5          | 55555555559090909090 | 3           | 35300,00        |
Транзакции и обработка ошибок в SQL Server

Транзакция — пример с 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
Транзакции и обработка ошибок в SQL Server

Транзакция — пример с 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 |
Транзакции и обработка ошибок в SQL Server

Транзакция — пример с 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
Транзакции и обработка ошибок в SQL Server

Транзакция — пример с 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        |
Транзакции и обработка ошибок в SQL Server

Транзакция — без явного указания транзакции

  • Счёт 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!
Транзакции и обработка ошибок в SQL Server

Транзакция — без явного указания транзакции

  • Счёт 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 |
Транзакции и обработка ошибок в SQL Server

Давайте потренируемся!

Транзакции и обработка ошибок в SQL Server

Preparing Video For Download...