Applied Database II

1. Applied Database II Transactions In Database

2. The ACID Test • Atomicity • The whole transaction or none of it • Consistency • Remains in a consistent state - Integrity • Isolation • Insulated from other operations • Durability • Changes are permanent

3. Transactions • Ensure that the boundaries of the transaction are known • Three types • Auto commit – any statement that modifies data • Explicit – specifically declared • Implicit – applies to a connection • “A Transact-SQL batch is not a transaction unless stated explicitly.”

4. Transactions • Transactions Ensure That Multiple Data Modifications Are Processed Together • Locks Prevent Update Conflicts • Transactions are serializable • Locking is automatic • Locks allow concurrent use of data • Concurrency Control

5. Considerations for Using Transactions • Transaction Guidelines • Keep transactions as short as possible • Use caution with certain Transact-SQL statements • Avoid transactions that require user interaction • Issues in Nesting Transactions • Allowed, but not recommended • Use @@trancount to determine nesting level

6. Setting the Implicit Transactions Option • Automatically Starts a Transaction When You Execute Certain Statements • Nested Transactions Are Not Allowed • Transaction Must Be Explicitly Completed with COMMIT or ROLLBACK TRANSACTION • By Default, Setting Is Off SET IMPLICIT_TRANSACTIONS ON

7. Restrictions on User-defined Transactions • Certain Statements May Not Be Included • ALTER DATABASE • BACKUP LOG • CREATE DATABASE • DROP DATABASE • RECONFIGURE • RESTORE DATABASE • RESTORE LOG • UPDATE STATISTICS

8. Explicit Transactions BEGIN TRAN • Marks the start of statements that must be executed or aborted COMMIT TRAN • Saves all changes to data ROLLBACK TRAN • Reverses all changes to data

9. Concurrency Problems • Lost updates • Avoid by writing atomic UPDATE statements • Uncommitted dependency (“dirty read”) • Use READ COMMITTED isolation (default) • Inconsistent analysis (nonrepeatable read) • Use REPEATABLE READ isolation • Phantom reads • Use SERIALIZABLE isolation

10. Concurrency Problems Prevented by Locks • Lost Update • Uncommitted Dependency (Dirty Read) • Inconsistent Analysis (Nonrepeatable Read) • Phantoms Reads

11. Locks Types of locks • Basic Locks • Shared • Exclusive • Special Situation Locks • Update • Intent • Schema • Key-range

12. Session-Level Locking Options • Transaction Isolation Level • READ COMMITTED (DEFAULT) • READ UNCOMMITTED • REPEATABLE READ • SERIALIZABLE • Locking Timeout • Limits time waiting for a locked resource • Use SET LOCK_TIMEOUT

13. Table-Level Locking Options • Use with Caution • Can Specify One or More Locking Options for a Table • Use optimizer_hints Portion of FROM Clause in SELECT or UPDATE Statement • Overrides Session-Level Locking Options

14. Displaying Locking Information • Current Activity Window • sp_lock System Stored Procedure • SQL Profiler • Windows 2000/XP System Monitor • Additional Information

15. A Simple of Begin Transaction Structure Begin Transaction [<namaTransaction>] ......... ......... [Commit Transaction [<namaTransaction>] ] [Rollback Transaction [<namaTransaction>] ]

16. Example ----- aktifkan database Inventory use Inventory ---- titik awal transaksi Begin Transaction ---- set format tanggal dd-mm-yy Set DateFormat dmy; ---- update tabel Beli insert into Beli values (’ANEKA’, ’001/05/05’, ’15-05-05’, ’PS.001’, 24, 1200,); ---- pembelian mempengaruhi stok if exists(select * from Stok where KdBr=’Ps.001’) update Stok set Banyak= Banyak+24 else insert into Stok values (‘PS.001’, 24) ---- pembelian mempengaruhi Hutang

17. Cont’ if exists (select * from hutang where KdSpl=’ANEKA’ and NoFak=’001/05/05’ and TgFak=’15-05-05’) Update Hutang Set jumlah=jumlah+(24*1200) Else insert into Hutang values (’ANEKA’, ’001/05/05’, ’15-05-05’, ’PS.001’, (24* 1200)); ---- cek data jika jumlah hutang melebihi jumlah tertentu transaksi batal, ---- jika tidak transaksi disimpan permanen if (select sum(jumlah) from hutang where KdSpl=’ANEKA’) > 10000000 begin raiserror( ’Hutang lebih dari 10 juta, transaksi dibatalkan!’,1,1); Rollback Transaction; End Else Commit Transaction; ---- cek tabel select * from Beli; select * from Stok; select * from Hutang;

18. A Transaction in SP ---- aktifkan database Inventory use Inventory; Create Procedure InBeli @pKdSpl as Varchar(10), @pNoFak as Varchar(20), @pSTgFak as Varchar(10), @pKdBr as Varchar(10), @pBanyak as Money, @pHargaBeli as Money as ---- titik awal transaksi Begin Transaction --- set format tanggal dd-mm-yy set DateFormat dmy; --- update tabel Beli Insert Into Beli

19. Values (@pKdSpl, @pNoFak, @pSTgFak, @pKdBr, @pBanyak, @pHargaBeli); --- pembelian mempengaruhi stok if exists (select * from Stok where KdBr=@pKdBr) update Stok set Banyak=Banyak+@pBanyak else insert into Stok values (@pKdBr, @pBanyak); --- pembelian mempengaruhi hutang if exists (select * from Hutang where KdSpl=@pKdSpl and NoFak=@pNoFak and TgFak=@pSTgFak) update Hutang set Jumlah=Jumlah+(@pBanyak*@pHargaBeli) else insert into Hutang values (@pKdSpl, @pNoFak, @pSTgFak, @pBanyak*@pHargaBeli)); --- cek data, jika jumlah hutang melebihi jumlah tertentu transaksi batal, --- jika tidak transaksi disimpan permanen if (select sum(jumlah) from Hutang where begin raiserror( ‘Hutang lebih dari 10 juta, transaksi dibatalkan’,1,1); Rollback Transaction; End Else Commit Transaction; ---- jalankan stored procedure tersebut execute InBeli @pKdSpl = ‘ANEKA’, @pNoFak = ‘002/05/05’, @pSTgFak = ’16-05-05’, @pKdBr = ‘PS.002’, @pBanyak = 48, @pHargaBeli = 1200;