1 / 30

Module 10: Managing Transactions and Locks

Module 10: Managing Transactions and Locks. Module 10: Managing Transactions and Locks. Overview of Transactions and Locks Managing Transactions Understanding SQL Server Locking Architecture Managing Locks. Lesson 1: Overview of Transactions and Locks. What Are Transactions?

axel
Download Presentation

Module 10: Managing Transactions and Locks

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 10: Managing Transactions and Locks

  2. Module 10: Managing Transactions and Locks • 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 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. 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 • 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? 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

  7. Demonstration: Introducing Transactions In this demonstration, you will see how to: • Use a transaction for a simple data update • How to commit a transaction if successful • How to rollback a transaction in case of errors

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

  9. Autocommit 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 ensures the entire batch will rollback upon any runtime error; compile errors not affected by XACT_ABORT ON SET XACT_ABORT {ON | OFF }

  10. 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 BEGIN TRANSACTION fund_transfer EXEC debit_checking '100', 'account1' EXEC credit_savings '100', 'account2' COMMIT TRANSACTION

  11. 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

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

  13. 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 • Considerations for nested transactions • Allowed, but not recommendedUse @@trancount to determine nesting level

  14. Restricted Statements • Restricted statements cannot be included in any explicit transaction. • 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 (Deprecated) • System stored procedures that modify master

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

  16. 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

  17. Lockable Resources SQL Server can lock these resources:

  18. Types of Locks

  19. Lock Compatibility • Not all locks are compatible with other locks. For example:

  20. Lesson 4: Managing Locks • Session-Level Locking Options • Lock Escalation • Dynamic Locking • What Are Deadlocks? • Methods to View Locking Information

  21. 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

  22. Lock Escalation Lock escalation converts many fine-grain locks to fewer coarse-grain locks

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

  24. 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. Task 1 Resource 1 Task 2 Resource 2

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

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

  27. Lab: Managing Transactions and Locks • Exercise 1: Using Transactions • Exercise 2: Managing Locks Logon Information Estimated Time: 60 minutes

  28. Lab Scenario You are a database developer in the IT department of Adventure Works Bicycle manufacturing company. In order to avoid data integrity issues that are occurring, you need to enclose some of your business logic in transactions. You will: • Work with simple queries to ensure that you understand the issues before you implement this in the live database. • Use test scenarios to understand how to resolve issues in the live database.

  29. Lab Review • How do you explicitly roll back a transaction? • Which Dynamic Management View would you query to get information about transaction locks? • What effect does the SERIALIZABLE isolation level have?

  30. Module Review and Takeaways • Review Questions • Common Issues and Troubleshooting Tips • Real-world Issues and Scenarios • Best Practices

More Related