Lock SQL Server ...2

The following table lists the types of locks available with SQL Server:

Intent --> The intent lock shows the future intention of the lock manager to acquire locks on a specific unit of data for a particular transaction. SQL Server uses intent locks to queue exclusive locks, thereby ensuring that these locks will be placed on the data elements in the order the transactions were initiated. Intent locks come in three flavors: intent shared (IS), intent exclusive (IX) and shared with intent exclusive (SIX). IS locks indicate that the transaction will read some (but not all) resources in the table or page by placing shared locks. IX locks indicate that the transaction will modify some (but not all) resources in the table or page by placing exclusive locks. SIX locks indicate that the transaction will read all resources, and modify some of them (but not all). This will be accomplished by placing the shared locks on the resources read and exclusive locks on the rows modified. Only one SIX lock is allowed per resource at one time, therefore, SIX locks prevent other connections from modifying any data in the resource (page or table), although they do allow reading the data in the same resource.

Shared --> Shared locks (S) allow transactions to read data with SELECT statements. Other connections are allowed to read the data at the same time; however, no transactions are allowed to modify data until the shared locks are released. Shared locks are normally released as soon as the data is read. However, there are ways to override this default behavior through query hints and transaction isolation levels.

Keep watching for more information...