Context:Given below a grid of rules applicable for different screen in a financial application.
Objective: To come up with a design that supports the implementation of the given requirement.

Design:

Resource level locks in Sql Server

1. Row
2. Key
3. Table
4. Page
5. Extent
6. Partition
7. Database

Models of locks

1. Shared Lock(S) - Can be held by any processes
2. Update Lock(U) - Mix of shared and exclusive lock
3. Exclusive Lock(X) - Can be held by only one process
4. Intent Lock(I)
   • Intent Shared(IS)
   • Shared with Intent Exclusive(SIX)
   • Intent Exclusive(IX)
5. Bulk Update Lock(BU)
6. Schema Lock
   • Schema Stability (Sch-S) - It is applied while generating the execution plan
   • Schema Modification (Sch-M) - It is applied while executing a DDL Statement

Transaction Isolation Levels

1. Pessimistic
   • Read Uncommitted (NOLOCK) - No shared lock acquired
   • Read Committed (READCOMMITTED) (Default) - Shared lock acquired and released immediately
   • Repeatable Read (REPEATABLEREAD) - Lock till the end of transaction
   • Serialization (HOLDLOCK) - Lock till the end of transaction and a range of rows
2. Optimistic
   • Snapshot
   • Snapshot Read Committed

1. sys.dm_tran_locks view keeps track of a Lock and resource identification
2. sys.dm_exec_sessions view provides transaction isolation level in use for the current process