SQL Server Locking Mechanism Quick Facts

1. The size of memory made available to the SQL server defines the lock granularity that the server will pick while processing a transaction.
   
2. The lowest granularity level is row.
   
3. SQL server gets shared locks on data being queried which means all queries can see data, but queries will block writes and writes will block queries, unlike Oracle which uses snapshots for executing queries, so queries will not block writes and writes does not block queries (although writes blocks other writes).
   
4. For updating a single row, SQL server acquires a single lock but if you are updating a huge set of rows, viz. 1000 rows, SQL server might decide on acquiring a page, extent or whole table lock depending on how the data is stored physically. One can control this by specifying ROWLOCK HINT in the update statement. Although tuning the query using HINTs should be done only under expert supervision or by experts.
   
5. SQL server acquires/ chooses Bulk Update lock for Bulk copy operations which improves performance at the cost of concurrency.
   
6. TRANSACTION ISOLATION LEVEL defined can affect the SQL server's choice of deciding on one level of lock over the other.
   
7. SERIALIZABLE is the most restrictive of all the transaction isolation levels (READ COMMITED, READ UNCOMMITED, REPEATABLE READ, SERIALIZABLE). It ensures that each transaction is completely isolated from others.
   
8. By default, SQL Server transactions do not time out, unless LOCK_TIMEOUT is specified.
   
9. SQL Server has deadlock detection and resolution mechanism which picks one of the transaction thread involved in deadlock to roll back. One can control which transaction gets rolled back using SET DEADLOCK_PRIORITY (LOW, NORMAL, HIGH or integer range from -10 to 10, default is NORMAL) statement. The transaction session with lower priority is picked to roll back in deadlock situations. For transaction sessions with same deadlock priority level, the one which is least expensive to roll back is picked and if nothing can be decided for the pick, the transaction to roll back is picked randomly.