Module 11: Managing Transactions and Locks

1. Module 11: Managing Transactions and Locks

2. Overview • Overview of Transactions and Locks • Managing Transactions • Understanding SQL Server Locking Architecture • Managing Locks

3. Lesson 1: Overview of Transactions and Locks • What Are Transactions? • What Are Locks? • What Is Concurrency Control?

4. What Are Transactions? • A transaction is a logical unit of work that displays ACID properties • Atomicity • Consistency • Isolation • Durability • Transactions ensure that multiple data modifications are processed together or not at all • The transaction log ensures that updates are complete and recoverable • Transactions use locks

5. What Are Locks? • Two main types of lock: • Read locks – allow others to read but not write • Write locks – stop others reading or writing • Deadlocks can occur • Locks prevent update conflicts • Locking ensures that transactions are serialized • Locking is automatic • Locks enable concurrent use of data

6. What Is Concurrency Control? • Pessimistic • Locks data when data is read in preparation for an update • Other users are blocked until lock is released • Use where high contention for data exists • Optimistic • Locks data when an update is performed • Error received if data was changed since initial read • Use when low contention for data exists

7. Lesson 2: Managing Transactions • Autocommit Transactions • Explicit Transactions • Implicit Transactions • Transaction Recovery • Considerations for Using Transactions • Restricted Statements

8. Autocommit Transactions • Default transaction mode • Every statement is committed or rolled back when it has completed • If it completes successfully – it is committed • If it fails – it is rolled back • Compile errors result in a batch not being executed

9. Explicit Transactions • BEGIN TRANSACTION • COMMIT TRANSACTION • ROLLBACK TRANSACTION • SAVE TRANSACTION • Transaction log BEGIN TRANSACTION fund_transfer EXEC debit_checking '100', 'account1' EXEC credit_savings '100', 'account2' COMMIT TRANSACTION

10. Implicit Transactions • Setting implicit transaction mode on • An implicit transaction starts when one of the following statements is executed • Transaction must be explicitly completed with COMMIT or ROLLBACK TRANSACTION SET IMPLICIT_TRANSACTION ON • INSERT • OPEN • REVOKE • SELECT • TRUNCATE TABLE • UPDATE • ALTER DATABASE • CREATE • DELETE • DROP • FETCH • GRANT

11. Transaction Recovery TransactionRecovery Action Required 1 None 2 Roll forward 3 Roll back 4 Roll forward 5 Roll back Checkpoint System Failure

12. Considerations for Using Transactions • Keep transactions as short as possible • Use caution with certain Transact-SQL statements • Avoid transactions that require user interaction • Do not browse data during a transaction • Affect the least rows possible with DML statements • Access the least rows possible with SELECT statements • Issues with nested transactions • Allowed, but not recommended • Use @@trancount to determine nesting level

13. Restricted Statements • Certain statements may not be included in explicit transactions, such as: • Full-text system stored procedure calls may not be included in explicit transactions • You cannot use the following in implicit or explicit transactions: • sp_dboption • System stored procedures that modify master • ALTER DATABASE • BACKUP • CREATE DATABASE • DROP DATABASE • RECONFIGURE • RESTORE DATABASE • RESTORE • UPDATE STATISTICS

14. Lesson 3: Understanding SQL Server Locking Architecture • What Concurrency Problems Are Prevented by Locks? • Lockable Resources • Types of Locks • Lock Compatibility

15. What Concurrency Problems Are Prevented by Locks? • Lost updates • Uncommitted dependencies (dirty read) • Inconsistent analysis (nonrepeatable read) • Phantom reads

16. Lockable Resources

17. Types of Locks • Basic locks • Shared • Exclusive • Special situation locks • Intent • Update • Schema • Bulk update

18. Lock Compatibility • Some locks are compatible with other locks, and some locks are not • Examples • Shared locks are compatible with all locks except exclusive • Exclusive locks are not compatible with any other locks • Update locks are compatible only with shared locks

19. Lesson 4: Managing Locks • Session-Level Locking Options • Dynamic Locking • What Are Deadlocks? • Methods to View Locking Information • Best Practices

20. Session-Level Locking Options • Transaction isolation level • READ UNCOMMITTED • READ COMMITTED (default) • REPEATABLE READ • SERIALIZABLE • Locking timeout • Limits time waiting for a locked resource • Use SET LOCK_TIMEOUT

21. Dynamic Locking Locking Cost Concurrency Cost DynamicLocking Cost Row Page Table Granularity

22. What Are Deadlocks? Supplier Part A A B • How SQL server ends a deadlock • How to minimize deadlocks • How to customize the lock time-out setting Transaction A Transaction B UPDATE Supplier UPDATE Part UPDATE Part UPDATE Supplier

23. Methods to View Locking Information • Activity Monitor window • sys.dm_tran_locks Dynamic Management View • EnumLocks method • SQL Server Profiler • Windows 2003 System Monitor

24. Best Practices Keep transactions short ü ü Design transactions to minimize deadlocks ü Check for error 1205 ü Set LOCK_TIMEOUT with care ü Monitor locking information

25. Lab: Managing Transactions and Locks • Exercise 1: Using Transactions • Exercise 2: Managing Locks