What is Optimistic and Pessimistic Locking in SQL Server ?

Optimistic Locking is a strategy where you read a record, take note of a version number and check that the version hasn't changed before you write the record back. When you write the record back you filter the update on the version to make sure it's atomic. (i.e. hasn't been updated between when you check the version and write the record to the disk) and update the version in one hit.

If the record is dirty (i.e. different version to yours) you abort the transaction and the user can re-start it.

This strategy is most applicable to high-volume systems and three-tier architectures where you do not necessarily maintain a connection to the database for your session. In this situation the client cannot actually maintain database locks as the connections are taken from a pool and you may not be using the same connection from one access to the next.

Pessimistic Locking is when you lock the record for your exclusive use until you have finished with it. It has much better integrity than optimistic locking but requires you to be careful with your application design to avoid Deadlocks. To use pessimistic locking you need either a direct connection to the database (as would typically be the case in a two tier client server application) or an externally available transaction ID that can be used independently of the connection.

There are 4 levels of locking in the pessimistic isolation levels from lowest to highest. They are :

1. Read Uncommitted 2. Read Committed 3. Repeatable read 4. Serializable 

Types Of Locks :

1. Shared Locks -> Used for operation that do not change or update data. 

2. Update Locks -> Used on resources that can be updated. 

3.Exclusive locks -> Used for data modelling operations such as INSERT ,UPDATE, DELETE .

4. Intent Locks -> Used To establish a lock hierarchy.

5. Schema Locks -> Used when an operation dependent on the schema of table .

6. Bulk Update Locks -> Used when bulk copying data into a table and the TABLOCK hint is specified.