Knowing which objects get locked and when goes a long way towards helping you develop applications that are more robust and predictable. SQL uses the transaction isolation level exclusively to determine what lock mode is applied to which objects. Understanding how this translates into object locks and lock modes is key to communicating your application's intentions to the SQL engine.

The strongest locks are held when the transaction isolation level is SERIALIZABLE, and the weakest locks are held when the transaction isolation level is READ UNCOMMITTED. This also translates into application concurrency—the higher the transaction isolation level, the less concurrent your application will be.