1 / 33

Mastering Concurrent SQL Server 2008 R2 Applications: Transactions and Locking Explained

This module provides an in-depth exploration of creating highly concurrent applications in SQL Server 2008 R2. Learn essential concepts related to transactions, including auto-commit, explicit, and implicit transactions. Understand locking mechanisms and their importance in concurrency control. Gain insights into transaction recovery, isolation levels, and management of locking. Essential considerations for optimizing transaction performance and preventing deadlocks are also covered. The module includes demonstrations to solidify your understanding of these critical aspects of SQL Server operations.

kioko
Télécharger la présentation

Mastering Concurrent SQL Server 2008 R2 Applications: Transactions and Locking Explained

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Module 11 Creating Highly Concurrent SQL Server® 2008 R2 Applications

  2. Module Overview • Introduction to Transactions • Introduction to Locks • Management of Locking • Transaction Isolation Levels

  3. Lesson 1: Introduction to Transactions • What are Transactions? • Auto Commit Transactions • Explicit Transactions • Implicit Transactions • Transaction Recovery • Considerations for using Transactions • Demonstration 1A: Transactions

  4. What are Transactions? A transaction is an atomic unit of work A transaction leaves data in a consistent state A transaction is isolated from other concurrent transactions A transaction is durable

  5. Auto Commit Transactions • Default transaction mode • Every TSQL statement is committed or rolled back when it has completed. Committed if successful; Rolled back if error • Compile errors result in entire batch not being executed • Run time errors may allow part of the batch to commit • Database engine operates in autocommit until an explicit transaction is started. • XACT_ABORT setting ON converts statement terminating errors into batch terminating errors; compile errors not affected by XACT_ABORT ON SETXACT_ABORTON;

  6. Explicit Transactions A transaction in which start and end of transaction is explicitly declared • BEGIN TRANSACTION • COMMIT TRANSACTION • ROLLBACK TRANSACTION • SAVE TRANSACTION • Transaction Log – Use WITH MARK to specify transaction marked in log BEGINTRANSACTIONFundsTransfer; EXECBanking.DebitAccount'100','account1'; EXECBanking.CreditAccount'100','account2'; COMMITTRANSACTION;

  7. Implicit Transactions • Setting implicit transaction mode on • An implicit transaction starts when one of the following statements is executed and the statement is not part of an existing transaction • Transaction must be explicitly completed with COMMIT or ROLLBACK TRANSACTION SETIMPLICIT_TRANSACTIONS ON;

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

  9. Considerations for using Transactions • Keep transactions as short as possible • Do not require user input Do not browse data Access the least amount of data possible Do not open the transaction before it is required • Try to access resources in the same order • Accessing resources in the same order within transactions can help avoid deadlocks • This is not always possible • Considerations for nested transactions • Allowedby syntax but true nesting not supportedUse @@trancount to determine nesting levelWhen a nested transaction rolls back, it rolls back the outer transaction as well

  10. Demonstration 1A: Transactions In this demonstration you will see • how transactions work • how blocking affects other users Note that blocking is discussed further in the next lesson.

  11. Lesson 2: Introduction to Locks • Methods of Concurrency Control • What are Locks? • Blocking vs. Locking • What Concurrency Problems are Prevented by Locking • Lockable Resources • Types of Locks • Lock Compatibility

  12. Methods of Concurrency Control Two main concurrency control types: • Pessimistic -Locks data when data is read in preparation for update -Other users are blocked until lock is released -Use where a high contention for data exists • Optimistic -Locks data when an update is performed -Error received if data is changed since initial read -Use where a low contention for data exists

  13. What are Locks? • Mechanism to sync access by multiple users to the same data at the same time • Two main types of lock: Read locks – Allow others to read but not writeWrite locks – Stop others from reading or writing • Locks prevent update conflicts Locking ensures that transactions are serialized Locking is automatic Locks enable concurrent use of data

  14. Blocking vs. Locking These two terms are often confused: • Locking - The action of taking and potentially holding locks - Used to implement concurrency control • Blocking -Normal occurrence for systems using locking - One process needs to wait for another process to release locked resources - Only a problem if it lasts too long

  15. What Concurrency Problems are Prevented by Locking? Without locking mechanisms, the following problems can occur: • Lost updates • Uncommitted dependency (dirty read) • Inconsistent analysis (non-repeatable read) • Phantom reads • Missing and double reads caused by row updates

  16. Lockable Resources SQL Server can lock these resources:

  17. Types of Locks

  18. Lock Compatibility • Not all locks are compatible with other locks. As a simple (but incomplete) example: • Refer to Books Online for a complete list

  19. Lesson 3: Management of Locking • Locking Timeout • Lock Escalation • What are Deadlocks? • Locking-related Table Hints • Methods to View Locking Information • Demonstration 3A: Viewing Locking Information

  20. Locking Timeout • How long should you wait for a lock to be released? • SET LOCK_TIMEOUT specifies number of milliseconds to wait • -1 (default) waits forever • When timeout expires, error is returned and statement rolled back • Not used often as most applications include query timeouts • READPAST locking hint – available but rarely used SETLOCK_TIMEOUT 5000;

  21. Lock Escalation • Large numbers of rows are often processed • This brings a need for large numbers of locks • Acquiring and releasing a large number of locks can have a significant impact on processing performance and memory availability • SQL Server will escalate from row locks to the table level as needed • For partitioned tables, it can escalate to the partition level Lock escalation converts many fine-grain locks to fewer coarse-grain locks

  22. What are Deadlocks? • Occurs when two or more tasks permanently block each other by each task having a lock on a resource which the other tasks are trying to lock. -Task T1 has a lock on resource R1 (arrow from R1 to T1) and has requested a lock on resource R2 (arrow from T1 to R2). -Task T2 has a lock on resource R2 (arrow from R2 to T2) and has requested a lock on resource R1 (arrow from T2 to R1). -Because neither task can continue until a resource is available and neither resource can be released until a task continues, a deadlock state exists. SQL Server automatically detects this situation and raises an error 1205 Task 1 Resource 1 Task 2 Resource 2

  23. Locking-related Table Hints

  24. Methods to View Locking Information Activity Monitor Dynamic Management Views SQL Server Profiler Reliability and Performance Monitor

  25. Demonstration 3A: Viewing Locking Information In this demonstration, you will see how to: • View lock information using Activity Monitor • Use dynamic management views to view lock info

  26. Lesson 4: Transaction Isolation Levels • SQL Server Transaction Isolation Levels • Read Committed Snapshot • Isolation-related Table Hints

  27. SQL Server Transaction Isolation Levels • Transaction Isolation Level can be set at the session level separately for each transaction

  28. Read Committed Snapshot • SNAPSHOT isolation level is useful but typically requires modifications to the application • In particular many reporting applications could benefit from it • Read Committed Snapshot is a database option that requires no modifications to the application • Statements that use Read Committed are automatically promoted to use Read Committed Snapshot instead • Locks are only held for the duration of the statement, not the duration of the transaction ALTERDATABASE Sales SETALLOW_SNAPSHOT_ISOLATIONON; ALTERDATABASE Sales SETREAD_COMMITTED_SNAPSHOTON;

  29. Isolation-related Table Hints

  30. Lab 11: Creating Highly Concurrent SQL Server Applications • Exercise 1: Detecting Deadlocks • Challenge Exercise 2: Investigating Transaction Isolation Levels (Only if time permits) Logon information Estimated time: 45minutes

  31. Lab Scenario In this lab, you will perform basic investigation of a deadlock situation. You are trying to determine an appropriate transaction isolation level for a new application. If you have time, you will investigate the trade-off between concurrency and consistency.

  32. Lab Review • What transaction isolation levels does SQL Server offer? • How does blocking differ from locking?

  33. Module Review and Takeaways • Review Questions • Best Practices

More Related