1 Lock Management 1

1.1 Types Of Locks: 1

1.1.1 Shared Lock (S) 1

1.1.2 Exclusive Lock(X) 1

1.1.3 Schema Stability Lock  (Sch-S) 1

1.1.4 Schema Modify Lock(Sch-M) 1

1.1.5 Update Lock(U) 1

1.1.6 Intent Lock (I) 1

1.1.7 Metadata Locks(Md) 1

1.2 Useful Lock Info 1

1. Lock Management

• every operation you perform on database results in one or more "locks".   locks are memory resources applied on database objects (tables, views,..) & database structures (files, pages..)

• These locks are used to control the concurrent (parallel) access to the database.  lock manager @ architecture manages all these locks.

1.  Types Of Locks: 

1. Shared Lock (S)

• Applicable For Read Or Fetch Operations

2. Exclusive Lock(X)

• Applicable For Write Or Dml Operations

3. Schema Stability Lock (Sch-S)

• Applicable For Metadata Reads (Ex: Sp_help, Sys.Tables..)

4. Schema Modify Lock(Sch-M)

• Applicable For Metadata Writes ( Ex: Create, Alter, Drop)

5. Update Lock(U)

• Applicable For Data Updates Using Tables & Views

6. Intent Lock (I)

• Applicable For S & X Locks. To Prevent Lower Level Resources (Pag) From Being Locked By Higher Level Resources (Table)

7. Metadata Locks(Md)

• Applicable For Operations On The Master Pages (From Master Extent)

2. Useful Lock Info

-- HOW TO KNOW THE NUMBER OF OPEN TRANSACTIONS IN THE CURRENT SESSION?

SELECT @@TRANCOUNT 

-- HOW TO KNOW THE DETAILS OF OPEN TRANSACTIONS IN THE CURRENT DATABASE?

DBCC OPENTRAN()

/*

QUERY BLOCKING CAN BE AVOIDED BY IMPLEMENTING ONE OF THE BELOW MECHANISMS:

1. LOCK HINTS

2. ISOLATION LEVELS

*/