1 / 22

Module 15: Managing Transactions and Locks

Module 15: Managing Transactions and Locks. Overview. Introduction to Transactions and Locks Managing Transactions SQL Server Locking Managing Locks. Introduction to Transactions and Locks. Transactions Ensure That Multiple Data Modifications Are Processed Together

kmerrick
Download Presentation

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

  2. Overview • Introduction to Transactions and Locks • Managing Transactions • SQL Server Locking • Managing Locks

  3. Introduction to 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 • Concurrency Control

  4. Managing Transactions • Multimedia Presentation: SQL Server Transactions • Transaction Recovery and Checkpoints • Considerations for Using Transactions • Setting the Implicit Transactions Option • Restrictions on User-defined Transactions

  5. Multimedia Presentation: SQL Server Transactions

  6. Transaction Recovery and Checkpoints Transaction Log ZOT! Recovery Needed? NONE INSERT … DELETE … UPDATE … … Recovery Needed? ROLL FORWARD INSERT … DELETE … UPDATE … … Recovery Needed? ROLL BACK Time (and place in log) COMMIT INSERT … DELETE … UPDATE … … Recovery Needed? ROLL FORWARD CHECKPOINT Transaction Log INSERT … DELETE … UPDATE … … Recovery Needed? ROLL BACK COMMIT INSERT … DELETE … UPDATE … … Database COMMIT CRASH!!!

  7. Considerations for Using Transactions • Transaction Guidelines • Keep transactions as small 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

  8. Setting 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

  9. Restrictions on User-defined Transactions • Certain Statements May Not Be Included in a Transaction • ALTER DATABASE • BACKUP LOG • CREATE DATABASE • DROP DATABASE • RECONFIGURE • RESTORE DATABASE • RESTORE LOG • UPDATE STATISTICS

  10. SQL Server Locking • Concurrency Problems Prevented by Locks • Lockable Resources • Types of Locks • Lock Compatibility

  11. Concurrency Problems Prevented by Locks • Lost Update • Uncommitted Dependency (Dirty Read) • Inconsistent Analysis (Nonrepeatable Read) • Phantoms Reads

  12. Item Description RID Key Row identifier Row lock within an index Page Data page or index page Extent Group of pages Table Entire table Database Entire database Lockable Resources

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

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

  15. Managing Locks • Session-Level Locking Options • Dynamic Locking Architecture • Table-Level Locking Options • Deadlocks • Displaying Locking Information

  16. Session-Level Locking Options • Transaction Isolation Level • READ COMMITTED (DEFAULT) • READ UNCOMMITTED • REPEATABLE READ • SERIALIZABLE • Locking Timeout • Limits time waiting for a locked resource • Use SET LOCK_TIMEOUT

  17. Dynamic Locking Architecture DynamicLocking Cost Row Page Table Locking Cost Concurrency Cost Granularity

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

  19. Deadlocks • How SQL Server Ends A Deadlock • How to Minimize Deadlocks • How to Customize the Lock Time-Out Setting

  20. Displaying Locking Information • Current Activity Window • sp_lock System Stored Procedure • SQL Profiler • Windows 2000 System Monitor • Additional Information

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

  22. Review • Introduction to Transactions and Locks • Managing Transactions • SQL Server Locking • Managing Locks

More Related