Record locking in SQL-Server

Locking is the way that SQL Server manages concurrent users. Without locking, data would be left in an inconsistent state if multiple users change data. The nice thing to know is that SQL Server locks records automatically, using a certain type of lock called a lock mode. There are six lock modes that SQL Server uses:

1. Shared: used when an operation does not update data, such as using a SELECT Transact-SQL statement
2. Update: used when resources have the potential of being updated. This mode prevents deadlocking. (Deadlocking will be discussed in a later lesson in this module.)
3. Exclusive: used in data modification operations, such as INSERT, UPDATE, and DELETE Transact-SQL statements (This mode makes sure that two transactions cannot modify the same data at the same time. No other lock will be granted by SQL Server if there is an exclusive lock present.)
4. Intent: establishes a locking hierarchy by acting as a queue for transactions that have the intention of achieving an exclusive lock
5. Schema: used when the schema of a table changes
6. Bulk Update:used when bulk copying data with the BCP program (Bulk copy is discussed in another course in this series.)

In SQL Server, both optimistic and pessimistic record locking are used to manage concurrency, that is, how multiple users access and modify data simultaneously. Each locking strategy has its scenarios where it is more appropriate, depending on the nature of the application and its specific requirements for data integrity and performance.

1. Pessimistic Record Locking: Pessimistic locking is used when you anticipate a high level of contention for data. In this strategy, SQL Server locks the data object as soon as it is read, and it holds the lock until the transaction is complete. This approach minimizes the chance of conflicts because it prevents other users from modifying the data at the same time. However, it can lead to reduced system performance and increased lock contention, potentially resulting in deadlocks.
   Use Cases for Pessimistic Locking:
   • High-risk transactions where data integrity is crucial, and the cost of a conflict would be very high.
   • Systems where data collisions are common, such as in applications where multiple users frequently update the same data.
   • Scenarios where the cost of locking resources is less significant than the potential damage or disruption of data conflicts.


2. Optimistic Record Locking: Optimistic locking is used when you expect low contention for data. It is based on the assumption that multiple transactions can frequently complete without interfering with each other. Under this model, data is read without acquiring locks, and modifications are attempted only at the point of transaction commit. At this time, SQL Server checks whether another transaction has modified the data after it was first read. If it has, the transaction can be rolled back or the application can handle the conflict.
   Use Cases for Optimistic Locking:
   • Applications with low data contention where transactions generally do not interfere with each other.
   • Read-heavy applications where data modifications are rare, minimizing the probability of conflicts.
   • Systems that prioritize system performance and responsiveness, as this method reduces the number of locks and thus the overhead on the database.

Choosing the Right Strategy
In SQL Server, these locking behaviors can be managed at various levels, including at the database level, the connection level, or even within individual transactions, using SQL commands or transaction isolation levels to adjust how locks are handled.

This is a longer term directive than ONLINE and is a very, very advanced topic. For purposes of this module and taking into consideration how much I have introduced so far on locking, let us stick with a simple explanation. Through much of the course thus far, I have repeatedly used the term lock. As I explained early on, this is something of a placeholder to avoid conflicts in data integrity. The ALLOW settings you are looking at here determine whether this index will allow row or page locks and this falls under the heading of extreme performance tweak.

Even though SQL Server handles locking automatically for you, do not think that you are going to get off that easily. You must decide between two locking strategies to implement in your database, as follows:

1. Pessimistic locking:Records are locked when they are read within a transaction, preventing any user from making any changes before the transaction is completed. This ensures that updates succeed, unless a deadlock occurs. This is the default locking method used by MS SQL Server 2000.
2. Optimistic locking: A method whereby records are NOT locked when they are read within a transaction. This allows users to make changes to the database. The records are locked only when they are updated. This introduces the possibility that two transactions modify data at the same time. Although this is not the default locking method used by MS SQL Server 2012, it can be easily implemented by using cursors. This is because a cursor allows you to control an individual record in a recordset. Cursors are covered in a prior module in this course.

The next lesson introduces you to issues relating to locking, called deadlocks.