What is Lock?As we all know, multiple users need to access databases concurrently. So locks come into the picture to prevent data from being corrupted or invalidated when multiple users try to do operations such as read, write and update on database.

“Lock is defined as a mechanism to ensure data integrity, consistency while allowing concurrent access to data. It is used to implement concurrency control when multiple users access Database to manipulate its data at the same time”

Where locks are put in Database:Now, we have to understand where locks are actually present in our database, it means on which resource it locks or not.

RID: (Row ID)

RID Used to lock a single row within a table.

Table: Complete table, including all data and indexes.

Key: Row lock within an index. It means primary key, Candidate Key, Secondary key etc.

Page: 8-kilobyte (KB) data page or index page. Lock can be place on Page Level also, it means if particular page is locks so another user cannot update data on it.

Extent: Contiguous group of eight data pages which can includes index pages also.

Database: Entire Database can be lock for some type of users which have read permission on database.

Different Models of locks:

  • Shared(S)

    a. Used for select operations
    b. Enable other sessions to perform select operations but prevent updates 
    c. read-only operations
    d. Operation with SELECT statement Generally use in Shared mode .

  • Exclusive(X)

    a. Used for DML operations
    b. Prevents other users from accessing the resource.
    c. Operations, such as INSERT, UPDATE, or DELETE means DML query. Ensures that multiple updates cannot be made to the same resource at the same time.

  • Update(U)

    a. Preliminary stage for exclusive lock. Used by the server when filtering the records to be modified
    b. Prevents other update locks
    c. A solution to the cycle deadlock problem

  • Intent

    a. Intent Locks are used for establish a lock Hierarchy.

              The types of intent locks are: 

1.intent shared (IS),

               2.intent exclusive (IX)

               3.shared with intent exclusive (SIX).

  • Schema

    a. Schema locks are used when an operation dependent on the schema of a table is executing.
    b. The types of schema locks are:
    c. Schema modification (Sch-M) and 
    d. Schema stability (Sch-S).

  • Bulk Update (BU)

    a. Bulk Update used when bulk-copying data into a table and the TABLOCK hint is specified. Generally use when user want to insert huge data in database/

Simplified Demonstration:

Lock mode Description
Shared (S) Used for read operations that do not change or update data, such as a SELECT statement. There are some cases where readers don’t acquire (S) locks.
Update (U) Those locks are the mix between shared and exclusive locks. SQL Server uses them with data modification statements while searching for the rows need to be modified. For example, if you issue the statement like: “update MyTable set Column1 = 0 where Column1 is null” SQL Server acquires update lock for every row it processes while searching for Column1 is null. When eligible row found, SQL Server converts (U) lock to (X).
Exclusive (X) Used for data-modification operations, such as INSERT, UPDATE, or DELETE. Ensures that multiple updates cannot be made to the same resource at the same time.
Intent (IS, IX, IU, etc) Used to establish a lock hierarchy. The types of intent locks are: intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX). Those locks indicate locks on the child objects. For example, if row has (X) lock, it would introduce (IX) locks on page, table and database level. Main purpose of those locks is optimization. This about situation when you need to have exclusive access to the database (i.e. (X) lock on database level). If SQL Server did not have intent locks, it would have to scan all rows in the all objects and see if there are any low level locks acquired.
Schema Used when an operation dependent on the schema of a table is executing. The types of schema locks are: schema modification (Sch-M) and schema stability (Sch-S).
Bulk Update (BU) Used when bulk copying data into a table and the TABLOCK hint is specified.
Key-range Protects the range of rows read by a query when using the serializable transaction isolation level. Ensures that other transactions cannot insert rows that would qualify for the queries of the serializable transaction if the queries were run again.

References:

https://msdn.microsoft.com/en-us/library/ms186396.aspx

http://aboutsqlserver.com/2011/04/28/locking-in-microsoft-sql-server-part-2-locks-and-transaction-isolation-levels/