------------------------------------------------------------------------------------ -- SQL Server Transaction Management ------------------------------------------------------------------------------------ -- * Transactions in SQL Server Basics -- * Explicit Transactions -- * Savepoints -- * Nesting Transactions -- * Transaction Recovery -- * Implicit Transactions -- * ACID Standards -- * Isolation Levels -- * Deadlocks ------------------------------------------------------------------------------------ -- Transactions in SQL Server ------------------------------------------------------------------------------------ -- * In SQL Server, By defualt, each DML commits automatically (AutoCommit). -- * This means that each statement is a transaction, and each transaction holds only one statement. USE Northwind UPDATE employees SET FirstName = 'Jojo' WHERE employeeID = 2; SELECT * FROM Employees; ROLLBACK; ------------------------------------------------------------------------------------ -- EXPLICIT TRANSACTIONS ------------------------------------------------------------------------------------ -- * If I want to group DML statements into one logical unit, and Commit or Rollback -- all as a group, I have to Explicitly open a transaction. BEGIN TRANSACTION UPDATE employees SET FirstName = 'Momo' WHERE employeeID = 1; UPDATE employees SET FirstName = 'Koko' WHERE employeeID = 3; SELECT * FROM Employees; ROLLBACK TRANSACTION; SELECT * FROM Employees; ------------------------------------------------------------------------------------ -- * WITH MARK - If I wish I can ask SQL Server to Mark my transaction in the -- Transaction Log, and even specify a short description for it. -- * It will then be able to identify it and use it during recovery. ------------------------------------------------------------------------------------ -- 1. Set up directories EXEC master.dbo.xp_create_subdir 'C:\MARK_DEMO' EXEC master.dbo.xp_create_subdir 'C:\MARK_DEMO\DATA' EXEC master.dbo.xp_create_subdir 'C:\MARK_DEMO\BACKUP' -- 2. Create Database USE master IF EXISTS (SELECT name FROM sys.databases WHERE name = 'MARK_DB') DROP DATABASE [MARK_DB] CREATE DATABASE [MARK_DB] ON PRIMARY ( NAME = N'MARK_DB', FILENAME = N'C:\MARK_DEMO\DATA\MARK_DB.mdf' , SIZE = 4096KB , FILEGROWTH = 1024KB ) LOG ON ( NAME = N'MARK_DB_log', FILENAME = N'C:\MARK_DEMO\DATA\MARK_DB_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%) -- 3. Create Table USE MARK_DB SELECT * INTO products FROM Northwind.dbo.products -- 4. Backup Database BACKUP DATABASE [MARK_DB] TO DISK = N'C:\MARK_DEMO\BACKUP\mark_db_full_backup.bak' -- 5. Change Data BEGIN TRANSACTION PriceUpdate WITH MARK 'UPDATE Products prices'; UPDATE products SET unitPrice = 1 COMMIT TRANSACTION PriceUpdate; SELECT * FROM products UPDATE products SET unitPrice = 2 BACKUP LOG [MARK_DB] TO DISK = N'C:\MARK_DEMO\BACKUP\mark_db_tlog_backup.trn' -- Time Passes. Regular database Actions and log backups are taken. -- An error occurs in the database. USE master RESTORE DATABASE [MARK_DB] FROM DISK = N'C:\MARK_DEMO\BACKUP\mark_db_full_backup.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 5 RESTORE LOG [MARK_DB] FROM DISK = N'C:\MARK_DEMO\BACKUP\mark_db_tlog_backup.trn' WITH FILE = 1, STATS = 10 , RECOVERY, STOPATMARK = 'PriceUpdate'; -- STOPBEFOREMARK / STOPATMARK / or simply use RECOVERY USE MARK_DB SELECT * FROM products ------------------------------------------------------------------------------------ -- SAVEPOINTS -- If I want to be able to rollback to a specific point in the transaction, -- I can establish a SAVEPOINT: ------------------------------------------------------------------------------------ USE Northwind SELECT * FROM employees BEGIN TRANSACTION UPDATE employees SET FirstName = 'Momo' -- SavePoint A ----------- SELECT * FROM employees SAVE TRANSACTION upd1; -------------------------- UPDATE employees SET FirstName = 'Koko' -- SavePoint B ----------- SELECT * FROM employees SAVE TRANSACTION upd2; -------------------------- UPDATE employees SET FirstName = 'Bobo' SELECT * FROM employees ROLLBACK TRAN upd2; SELECT * FROM employees ROLLBACK TRAN upd1; SELECT * FROM employees ROLLBACK -- End Of Tran -- Check Data SELECT * FROM employees ------------------------------------------------------------------------------------ -- Nesting Transactions - @@TRANCOUNT ------------------------------------------------------------------------------------ USE NORTHWIND -- The current transaction level nesting: SELECT @@TRANCOUNT -- (No Transaction open) BEGIN TRANSACTION SELECT @@TRANCOUNT -- (One Transaction) BEGIN TRANSACTION SELECT @@TRANCOUNT -- (Two Transactions - NESTED) COMMIT SELECT @@TRANCOUNT -- Only commits the last nested transaction. COMMIT SELECT @@TRANCOUNT ------------------------------ -- Demo : ------------------------------ SELECT * FROM employees SELECT @@TRANCOUNT BEGIN TRANSACTION UPDATE employees SET FirstName = 'Trn_1' SELECT @@TRANCOUNT SELECT * FROM employees BEGIN TRANSACTION SELECT @@TRANCOUNT UPDATE products SET productName = 'Trn_2' SELECT * FROM products COMMIT SELECT @@TRANCOUNT SELECT * FROM employees SELECT * FROM products ROLLBACK -- Always the Root Transaction. (The whole nested tree) SELECT * FROM employees SELECT * FROM products SELECT @@TRANCOUNT -- * Only if the first (Root) transaction is committed, all the nested transactions -- will also be committed. -- * They are like Sub-Transactions which are depnedent on it. -- * If the first transaction fails or rolls back, all nested transactions -- are also rolled back. (Even if they already committed) -- * This is why it is not recommended to use Nested Transactions. ------------------------------------------------------------------------------------ -- TRANSACTION RECOVERY / Instance Recovery ------------------------------------------------------------------------------------ -- The same as Oracle: -- * All committed changes are always documented in the Transaction Log. -- * From time to time SQL Server also updates the Dirty Pages in the Data Files. -- (CHECKPOINT). -- * The last Checkpoint is marked in the Transaction Log as the Checkpoint Position. -- * In case of a crash, When SQL Server goes up again he will Roll Forward all statements -- logged in the Transaction log, starting from the Checkpoint Position. -- * After the Roll Forward, He will Roll Back any transactions that did not commit. -- * All this happens automatically and is transaparent to us. ------------------------------------------------------------------------------------ -- IMPLICIT TRANSACTIONS ------------------------------------------------------------------------------------ -- * If I want to change SQL Server's default behavior for a specific connection. -- * Instead of doing AutoCommit, he will open Implicit Transactions. -- (Like Oracle's defualt behavior) SET IMPLICIT_TRANSACTIONS ON SELECT * FROM Employees WHERE EmployeeID = 2 ; UPDATE employees SET FirstName = 'Shuki' WHERE employeeID = 2; SELECT @@TRANCOUNT SELECT * FROM Employees WHERE EmployeeID = 2 ; ROLLBACK; SELECT * FROM Employees WHERE EmployeeID = 2 ; -- Back to default: SET IMPLICIT_TRANSACTIONS OFF ------------------------------------------------------------------------------------ -- * ACID Standard (a.k.a ACID test) ------------------------------------------------------------------------------------ -- In computer science, ACID (atomicity, consistency, isolation, durability) -- is a set of properties that guarantee database transactions are processed reliably. -- * Atomicity -------------- -- The principle of atomicity states that all parts of a transaction must -- complete, or none of them. -- For example, if your business analysts have said that every time you -- change an employee’s salary you must also change his grade, then the “atomic” -- transaction will consist of two updates. The database must guarantee that both -- go through, or neither. -- * Consistency ---------------- -- The principle of consistency states that the results of a query must be -- consistent with the state of the database at the time the query started. -- * Isolation -------------- -- The principle of isolation states that an incomplete (that is, uncommitted) -- transaction must be invisible to the rest of the world. -- * Durability. --------------- -- The principle of durability states that once a transaction completes with a -- COMMIT, it must be impossible for the database to lose it ------------------------------------------------------------------------------------ -- Create DEMO database and populate it with data SELECT * FROM sys.databases USE master IF EXISTS(SELECT name FROM sys.databases WHERE name = 'DEMO') DROP DATABASE DEMO CREATE DATABASE DEMO USE demo CREATE TABLE products (productID int identity(1,1) primary key, productName varchar(45), unitPrice int, update_counter int ) DECLARE @counter int SET @counter = 0 SET NOCOUNT ON WHILE @counter < 500 BEGIN INSERT INTO products SELECT productName + '-' + CAST(@counter as varchar) , unitPrice , 1 FROM northwind.dbo.products SET @counter = @counter + 1 END SET NOCOUNT OFF SELECT * FROM products SELECT COUNT(*) FROM products SELECT TOP 10 * FROM products ------------------------------------------------------------------------------------ -- Atomicity ------------------------------------------------------------------------------------ USE demo SELECT * FROM products WHERE productID = 10 BEGIN TRANSACTION UPDATE products SET unitPrice = unitPrice + 1 WHERE productID = 10 UPDATE products SET update_counter = update_counter + 1 WHERE productID = 10 COMMIT TRANSACTION SELECT * FROM products WHERE productID = 10 ------------------------------------------------------------------------------------ -- Consistency / Isolation ------------------------------------------------------------------------------------ --------------- -- Session 1 -- --------------- USE demo SELECT * FROM products WHERE productID = 10 --------------- -- Session 2 -- --------------- USE demo BEGIN TRANSACTION UPDATE products SET unitPrice = unitPrice + 1 WHERE productID = 10 -- COMMIT TRANSACTION / ROLLBACK TRANSACTION SELECT * FROM products WHERE productID = 10 --Wait select @@TRANCOUNT ROLLBACK TRANSACTION select @@TRANCOUNT --------------- -- Session 1 -- --------------- SELECT * FROM products WHERE productID = 10 SELECT * FROM products WHERE productID = 11 UPDATE products SET unitPrice = unitPrice + 1 WHERE productID = 10 UPDATE products SET productName = 'Moshe' WHERE productID = 10 ------------------------------------------------------------------------------------ -- Understanding Different Isolation Levels ------------------------------------------------------------------------------------ -- * Dirty Read - Reads data that is uncommitted. -- * Non Repeatable Read- If anybody performed UPDATE or DELETE of any of the rows you read earlier. -- * Phantom Rows - If anybody INSERTED a row within the range you had for an earlier query SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED -- * Dirty Read - Allowed -- * Non Repeatable Read - Allowed -- * Phantom Rows - Allowed SET TRANSACTION ISOLATION LEVEL READ COMMITTED -- * Dirty Read - Not Allowed -- * Non Repeatable Read - Allowed -- * Phantom Rows - Allowed SET TRANSACTION ISOLATION LEVEL REPEATABLE READ -- * Dirty Read - Not Allowed -- * Non Repeatable Read - Not Allowed -- * Phantom Rows - Allowed SET TRANSACTION ISOLATION LEVEL SERIALIZABLE -- * Dirty Read - Not Allowed -- * Non Repeatable Read - Not Allowed -- * Phantom Rows - Not Allowed SET TRANSACTION ISOLATION LEVEL SNAPSHOT ALTER DATABASE DEMO SET ALLOW_SNAPSHOT_ISOLATION ON -- * Dirty Read - Not Possible -- * Non Repeatable Read - Not Possible -- * Phantom Rows - Not Possible ------------------------------------------------------------------------------------ -- * Dirty Read ------------------------------------------------------------------------------------ SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED -- Session 1 -- USE demo SELECT * FROM products WHERE productID = 10 -- Session 2 -- USE demo USE demo BEGIN TRANSACTION UPDATE products SET unitPrice = 60 WHERE productID = 10 -- COMMIT TRANSACTION / ROLLBACK TRANSACTION SELECT * FROM products WHERE productID = 10 --Wait Rollback -- Session 1 -- SELECT * FROM products WHERE productID = 10 SET TRANSACTION ISOLATION LEVEL READ COMMITTED SELECT * FROM products WHERE productID = 10 ------------------------------------------------------------------------------------ -- * Non Repeatable Read - seeing other transaction's committed changes ------------------------------------------------------------------------------------ -- Session 1 -- BEGIN TRANSACTION SELECT * FROM products WHERE productID = 10 -- Session 2 -- USE demo GO UPDATE products SET unitPrice = 50 WHERE productID = 10 -- Session 1 -- -- Still part of the transaction SELECT * FROM products WHERE productID = 10 -- COMMIT TRANSACTION / ROLLBACK TRANSACTION ------------------------------------------------------------------------------------ -- * Phantom Rows - seeing other rows selected by where clause change as result -- of transaction ------------------------------------------------------------------------------------ UPDATE products SET productName = 'Fish5' WHERE productID = 10 -- Session 1 BEGIN TRANSACTION SELECT * FROM products WHERE productName = 'Fish5' -- Session 2 USE demo GO INSERT INTO products VALUES ('Fish4' , 80 , 1 ) -- Session 1 -- Still part of the transaction SELECT * FROM products WHERE productName = 'Fish4' -- COMMIT TRANSACTION / ROLLBACK TRANSACTION ------------------------------------------------------------------------------------ -- Dynamic Lock Mechanism ------------------------------------------------------------------------------------ SET TRANSACTION ISOLATION LEVEL READ COMMITTED USE demo DROP TABLE products CREATE TABLE products (productID int identity(1,1) primary key, productName varchar(45), unitPrice int, update_counter int ) DECLARE @counter int SET @counter = 0 SET NOCOUNT ON WHILE @counter < 500000 BEGIN INSERT INTO products VALUES ('Product' + '-' + CAST(@counter as varchar) , 50 , 1 ) SET @counter = @counter + 1 IF @counter % 20000 = 0 PRINT @counter END SELECT COUNT(*) FROM products SELECT TOP 10 * FROM products SELECT MAX(productID) FROM products -- Table Size SELECT t.NAME AS TableName, p.rows AS RowCounts, SUM(a.total_pages) * 8 AS TotalSpaceKB, SUM(a.used_pages) * 8 AS UsedSpaceKB, (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB FROM sys.tables t INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id WHERE t.NAME NOT LIKE 'dt%' AND t.is_ms_shipped = 0 AND i.OBJECT_ID > 255 GROUP BY t.Name, p.Rows ORDER BY t.Name -- Session 1 BEGIN TRAN UPDATE products SET unitPrice = unitPrice + 1 WHERE productID = 20 -- ROLLBACK -- Session 2 UPDATE products SET unitPrice = unitPrice + 1 WHERE productID = 20 UPDATE products SET unitPrice = unitPrice + 1 WHERE productID = 10 UPDATE products SET unitPrice = unitPrice + 1 WHERE productID = 77 SELECT allow_row_locks, allow_page_locks, type_desc FROM sys.indexes WHERE object_id = object_id('products'); ALTER TABLE products REBUILD WITH (ALLOW_PAGE_LOCKS = OFF); -- * If ALLOW_PAGE_LOCKS = OFF -- the lock manager will not take page locks on that index. The manager will only use row or table locks -- * If ALLOW_ROW_LOCKS = OFF -- the lock manager will not take row locks on that index. The manager will only use page or table locks. -- * If ALLOW_PAGE_LOCKS = OFF and ALLOW_PAGE_LOCKS = OFF -- locks are assigned at a table level only -- * If ALLOW_PAGE_LOCKS = ON and ALLOW_PAGE_LOCKS = ON , -- SQL decides on which lock level to create according to the amount of rows and memory available. ------------------------------------------------------------------------------------ -- Lock modes ------------------------------------------------------------------------------------ --------------------------------------------------- -- Shared locks (S) --------------------------------------------------- -- * Shared locks are held on data being read. -- * While a shared lock is being held other transactions can read but can't modify locked data. -- * After the locked data has been read the shared lock is released, unless the transaction is being -- run with the locking hint BEGIN TRAN USE demo SELECT * FROM products WITH (HOLDLOCK) WHERE productID = 2 SELECT resource_type, request_mode, resource_description, resource_associated_entity_id FROM sys.dm_tran_locks WHERE resource_type <> 'DATABASE' ROLLBACK --------------------------------------------------- -- Update locks (U) --------------------------------------------------- -- * Update locks are a mix of shared and exclusive locks. -- * When a DML statement is executed SQL Server has to find the data it wants to modify first. -- * Only one update lock can be held on the data at one time, similar to an exclusive lock. -- But the difference here is that the update lock itself can't modify the underlying data. -- It has to be converted to an exclusive lock before the modification takes place. -- You can also force an update lock with the UPDLOCK hint: BEGIN TRAN USE demo SELECT * FROM products WITH (UPDLOCK) WHERE productID = 2 SELECT resource_type, request_mode, resource_description, resource_associated_entity_id FROM sys.dm_tran_locks WHERE resource_type <> 'DATABASE' ROLLBACK --------------------------------------------------- -- Exclusive locks (X) --------------------------------------------------- -- * Exclusive locks are used to lock data being modified by one transaction thus preventing modifications by -- other concurrent transactions. You can read data held by exclusive lock only by specifying a NOLOCK -- hint or using a read uncommitted isolation level. -- * Because DML statements first need to read the data they want to modify you'll always find Exclusive -- locks accompanied by shared locks on that same data. BEGIN TRAN USE demo UPDATE products SET unitPrice = unitPrice + 1 WHERE productID = 10 SELECT resource_type, request_mode, resource_description, resource_associated_entity_id FROM sys.dm_tran_locks WHERE resource_type <> 'DATABASE' ROLLBACK --------------------------------------------------- -- Intent locks (I) --------------------------------------------------- -- * Intent locks are a means in which a transaction notifies other transaction that it is intending to lock the data. -- Thus the name. Their purpose is to assure proper data modification by preventing other transactions to acquire a -- lock on the object higher in lock hierarchy. What this means is that before you obtain a lock on the page or the row -- level an intent lock is set on the table --------------------------------------------------- -- Schema locks (Sch) --------------------------------------------------- -- * Schema modification lock (Sch-M): Used while executing a DDL statement. Blocks access to the object data since its structure is being changed. -- * In the example we can see the Sch-S and Sch-M locks being taken on the system tables and the TestTable plus a lot of other locks on -- the system tables. BEGIN TRAN USE DEMO CREATE TABLE TestTable (TestColumn INT) SELECT resource_type, request_mode, resource_description FROM sys.dm_tran_locks WHERE resource_type <> 'DATABASE' ROLLBACK SELECT * FROM TestTable --------------------------------------------------- -- Bulk Update locks (BU) --------------------------------------------------- -- * Bulk Update locks are used by bulk operations when TABLOCK hint is used by the import. This allows for multiple fast -- concurrent inserts by disallowing data reading to other transactions. SELECT dm_tran_locks.request_session_id, dm_tran_locks.resource_database_id, DB_NAME(dm_tran_locks.resource_database_id) AS dbname, CASE WHEN resource_type = 'object' THEN OBJECT_NAME(dm_tran_locks.resource_associated_entity_id) ELSE OBJECT_NAME(partitions.OBJECT_ID) END AS ObjectName, partitions.index_id, indexes.name AS index_name, dm_tran_locks.resource_type, dm_tran_locks.resource_description, dm_tran_locks.resource_associated_entity_id, dm_tran_locks.request_mode, dm_tran_locks.request_status FROM sys.dm_tran_locks LEFT JOIN sys.partitions ON partitions.hobt_id = dm_tran_locks.resource_associated_entity_id JOIN sys.indexes ON indexes.OBJECT_ID = partitions.OBJECT_ID AND indexes.index_id = partitions.index_id WHERE resource_associated_entity_id > 0 AND resource_database_id = DB_ID() ORDER BY request_session_id, resource_associated_entity_id --------------- -- DEADLOCKS -- --------------- -- Definition - Deadlock occurs when two or more sessions are waiting for each other, in such a way that none can complete. -- For example: -- Sessions 1 locks row 2, Session 2 locks row 3. -- Then session 1 attempts to lock row 3 and and session 2 attempts to lock row 2. -- SQL Server detects this and automatically rollback one of the transactions. (Shortest one) -- A 1205 error will be displayed. USE Northwind ----------------------- -- In first session: -- ----------------------- BEGIN TRANSACTION UPDATE employees SET FirstName = 'session 1' WHERE employeeID = 2; ------------------------ -- In second session: -- ------------------------ USE Northwind BEGIN TRANSACTION UPDATE employees SET FirstName = 'session 2' WHERE employeeID = 3; UPDATE employees SET FirstName = 'session 2' WHERE employeeID = 2; ----------------------- -- In first session: -- ----------------------- UPDATE employees SET FirstName = 'session 2' WHERE employeeID = 3; ROLLBACK ------------------------------------------- -- View current locks: -- System tables - sys.dm_tran_locks -- System Procedure - sp_lock -- Management Studio - Activity Monitor -- Profiler - Monitor Lock_Event SELECT resource_type, request_mode,request_type, request_status, request_session_id FROM sys.dm_tran_locks sp_lock -- View current Server Process ID: SELECT @@SPID SELECT * FROM sys.dm_exec_sessions where status <> 'sleeping' -- Kill A Session: KILL 53