1 / 25

Comprehensive Guide to Transactions and Locks Management in SQL Server

This module covers the principles of transactions and locks in SQL Server, including concurrency control, managing locks, and understanding the SQL Server locking architecture. Learn about transaction recovery, implicit transactions, considerations for using transactions, lockable resources, types of locks, deadlock prevention, and best practices.

rcandice
Download Presentation

Comprehensive Guide to Transactions and Locks Management in SQL Server

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

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

  3. Lesson: Introducing Transactions and Locks • What Are Transactions and Locks? • What Is Concurrency Control?

  4. What Are Transactions and Locks? • 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

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

  6. Lesson: Managing Transactions • Multimedia Presentation: SQL Server Transactions • How Transaction Recovery Works • Considerations for Using Transactions • The Implicit Transactions Option • Restricted Statements

  7. Multimedia Presentation: SQL Server Transactions Concepts covered in this presentation: • SQL Server Transaction Processing • Two Types of Transactions • Implicit • Explicit • Rolling Back a Transaction • The Transaction Log • Checkpoints • The Automatic Recovery Process

  8. How Transaction Recovery Works 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 • 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

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

  11. Restricted Statements • Certain Statements May Not Be Included • ALTER DATABASE • BACKUP LOG • CREATE DATABASE • DROP DATABASE • RECONFIGURE • RESTORE DATABASE • RESTORE LOG • UPDATE STATISTICS

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

  13. What Concurrency Problems Are Prevented by Locks? • Lost Update • Uncommitted Dependency (Dirty Read) • Inconsistent Analysis (Nonrepeatable Read) • Phantom Reads

  14. Lockable Resources

  15. Types of Locks • Basic Locks • Shared • Exclusive • Special Situation Locks • Intent • Update • Schema • Bulk update

  16. Lock Compatibility • Locks May or May Not Be Compatible with Other Locks • 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

  17. Lesson: Managing Locks • Session-Level Locking Options • What Is Dynamic Locking Architecture? • What Are Table-Level Locking Options? • What Are Deadlocks? • Methods to View Locking Information • Best Practices

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

  19. What Is Dynamic Locking Architecture? Locking Cost Concurrency Cost DynamicLocking Cost Row Page Table Granularity

  20. What Are Table-Level Locking Options? • Use with Caution • Can Specify One or More Locking Options for a Table • Use optimizer_hints Portion of FROM Clause inSELECT or UPDATE Statement • Overrides Session-Level Locking Options

  21. What Are Deadlocks? • How SQL Server Ends a Deadlock • How to Minimize Deadlocks • How to Customize the Lock Time-Out Setting

  22. Methods to View Locking Information • Current Activity Window • sp_lock System Stored Procedure • SQL Profiler • Windows 2000 System Monitor • Additional Information

  23. Best Practices ü Keep Transactions Short ü Design Transactions to Minimize Deadlocks ü Use SQL Server Defaults for Locking ü Be Careful When You Use Locking Options

  24. Lab A: Managing Transactions and Locks • Exercise 1: Creating and Executing a Transaction • Exercise 2: Rolling Back a Transaction • Exercise 3: Viewing Locking Information • Exercise 4: Setting Locking Options

  25. Course Evaluation

More Related