Transaksi
Transaksi dan Penanganan Kesalahan di SQL Server
Miriam Antona
Software Engineer
Dataset: transaksi bank
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 |
Dataset: transaksi bank
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 |
Dataset: transaksi bank
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 |
Apa itu transaksi?
- Transaksi: satu atau lebih pernyataan; semua dijalankan atau tidak sama sekali
Apa itu transaksi?
Transfer $100 akun A -> akun B
- Kurangi $100 dari akun A
- Tambah $100 ke akun B
Operasi 2 GAGAL -> Tidak dapat mengurangi $100 dari akun A!
Pernyataan transaksi - BEGIN memulai transaksi
BEGIN { TRAN | TRANSACTION }
[ { transaction_name | @tran_name_variable }
[ WITH MARK [ 'description' ] ]
]
[ ; ]
Pernyataan transaksi - COMMIT menyimpan transaksi
COMMIT [ { TRAN | TRANSACTION } [ transaction_name | tran_name_variable] ]
[ WITH ( DELAYED_DURABILITY = { OFF | ON } ) ][ ; ]
Pernyataan transaksi - ROLLBACK membatalkan transaksi
ROLLBACK { TRAN | TRANSACTION }
[ transaction_name | @tran_name_variable |
savepoint_name | @savepoint_variable ] [ ; ]
Transaksi - contoh
- Akun 1 = $24.400
- Akun 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;
Transaksi - contoh
- Akun 1 = $24.400
- Akun 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 |
Transaksi - contoh
- Akun 1 = $24.400
- Akun 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;
Transaksi - contoh
- Akun 1 = $24.400
- Akun 5 = $35.300
| account_id | account_number | customer_id | current_balance |
|------------|----------------------|-------------|-----------------|
| 1 | 55555555551234567890 | 1 | 24400,00 |
| 5 | 55555555559090909090 | 3 | 35300,00 |
Transaksi - contoh dengan TRY...CATCH
- Akun 1 = $24.400
- Akun 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
Transaksi - contoh dengan TRY...CATCH
- Akun 1 = $24.400
- Akun 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 |
Transaksi - contoh dengan TRY...CATCH
- Akun 1 = $24.400
- Akun 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
Transaksi - contoh dengan TRY...CATCH
- Akun 1 = $24.400
- Akun 5 = $35.300
| account_id | account_number | customer_id | current_balance |
|------------|----------------------|-------------|-----------------|
| 1 | 55555555551234567890 | 1 | 24400,00 |
| 5 | 55555555559090909090 | 3 | 35300,00 |
Transaksi - tanpa menentukan transaksi
- Akun 1 = $24.400
- Akun 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!
Transaksi - tanpa menentukan transaksi
- Akun 1 = $24.400
- Akun 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 |
Ayo berlatih!
Transaksi dan Penanganan Kesalahan di SQL Server
