Transactions (Part 2)

Good morning everyone! As promised, today we will be discussing Transaction Locking, which is one of the key elements to discovering exactly how transactions work with each other, particularly in an environment where multiple users will be using the same database in order to add and modify their data.  Although I haven't had the opportunity to look at this in the working environment, I am aware that it has been used on our database projects and it's a very important concept to have a handle on. When considering clients requirements for a database application, you need to consider the following 2 approaches; Pessimistic Control and Optimistic Control.

Pessimistic Control 

Pessimistic control assumes that the users will attempt to access and update the same data at the same time. In this situation, locks should be included to prevent this from happening.

Optimistic Control 

Optimistic control assumes that users will not be attempting to update the same data that regularly, therefore control can be relaxed. In this situation, less locks can be introduced and therefore speeding up the database.

In reality, you will need to look at the 2 different approaches and combine them to produce a number of locks and isolation levels (something which I will come onto in a later blog post). There are various different lock modes within SQL and each are used in their own situations. Some can be combined with other locks whilst others can only be used on a resource on their own. The list below (thanks to Microsoft SQL Server 2008 - Database Development (70-433) ISBN: 978-0-7356-2639-3) provides details on what the available locking modes are and under what conditions they are used:

Lock modes: 

Shared (S) 

• Used for read-only operations such as SELECT statements. 
• The Shared lock is compatible with other Shared locks.

Update (U)

• Used for both read and write operations, however only one transaction will be granted access at any one time. 
• The Update lock is usually upgraded to an Exclusive lock.

Exclusive (X)

• Used on all data modification operations (INSERT, UPDATE and DELETE).
• Ensures that multiple updates can't be made to the same data at the same time.
• Exclusive locks are not compatible with any other lock type. 

Intent (IS, IX, SIX)

• Improves performance by placing locks on tables and views before placing locks on page level controls. 

Schema (Sch-M, Sch-S)

• Sch-M stands for Schema Modification lock which is used when changes to the database schema occur, such as adding new tables or columns to an existing table. 
• A Sch-M lock will prevent any other executions until the current operation is complete.
• Sch-S stands for Schema Stability lock which is used when queries are being executed.
• A Sch-S lock can't be used with Sch-M locks.

Bulk Update (BU)

• Allows for a block insert into a table.
• Allows multi-threading however other processes won't be able to access the table until the bulk update is complete. 

Key-Range

• Protects against Phantom reads (where datasets brought back from 2 identical SELECT statements are different due to 2 DML transactions being executed at the same time).

Deadlock and Blocking 

Deadlocking occurs when two transactions updating the same data in the same data column on a database at the same time and, as a result, execution hangs as neither can be completed successfully. SQL Server determines which transaction should be rolled back by looking at the estimated cost for roll back (the lower cost will be rolled back). A 1205 error message will then be given, however ensure that these are captured effectively as they don't provide any information to users.

How to Reduce Deadlocks and Blocking 

In order to reduce the chances of deadlocking and transaction blocking from occurring:

• Keep transactions simple. 
• Verify all data input by users before running transactions.
• Access the least amount of data possible within your transactions. 
• Adjust query wait times.
• Assess possible deadlock areas by using SQL Server Profiler. 

Lock Status

You can determine lock status of a transaction by using SQL Server Profiler, which can also produce reports to show which locks have been placed on which database resources. Locks can be placed on the following resources: 

• KEY - Keys within an index. 
• PAGE - An 8KB page of data made up from tables. 
• EXTENT - A group of 8 pages.
• HoBT - A Heap or Balanced Tree Index.
• TABLE - A complete table and its contents, including Indexes. 
• FILE - A complete database file.
• APPLICATION - A resource related to the application that is running it. 
• METADATA - Specified metadata.
• ALLOCATION_UNIT - A single unit that has been allocated. 
• DATABASE - A complete database, including absolutely everything.

Next...

Thank you for reading this blog post! The next instalment will discuss Setting Transaction Isolation Levels. Watch this space everyone!