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

Optimistic record locking is used when it is expected that conflicts between concurrent transactions will be rare. This approach allows multiple transactions to read and update a record simultaneously, but it requires each transaction to check for conflicts before committing its changes. If a conflict is detected, the transaction must be rolled back and retried.
Pessimistic record locking is used when it is expected that conflicts between concurrent transactions will be frequent. This approach prevents multiple transactions from reading or updating a record simultaneously by placing a lock on the record as soon as it is accessed. Other transactions that attempt to access the locked record will be blocked until the lock is released. This can help to prevent conflicts, but it can also lead to blocking and deadlocks if not used carefully.
In general, Optimistic locking is preferred in situations where contention is expected to be low and Pessimistic locking is preferred in situations where contention is expected to be high.

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.