Database locks in SQL Server table
SQL Server holds the locks during the execution of the queries. If the statements holding the locks complete, then the locks are released. There is a way to tell SQL Server not to honor the locks and read DIRTY data (data that is modified, yet not committed). This has some serious consequences if NOT used correctly. The command is NOLOCK or read uncommitted transaction isolation level.
I have used nolock and am using nolocks as I work on large databases with high traffic systems but not in every place and every statement. This is ok as long as you know the consequences and DO NOT use in banking related transactions.You can't simply clear all locks from a table. Locks are held for a reason to maintain the ACID (Atomicity, Consistency, Isolation, and Durability)
ACID Properties of Transactions
Atomicity : Atomicity means all or nothing. Transactions often contain multiple separate actions.
Consistency : Consistency means that transactions always take the database from one consistent state to another
Isolation : Isolation means that concurrent transactions, and the changes made within them, are not visible to each other until they complete.
Durability : Durability means that committed transactions will not be lost, even in the event of abnormal termination.
If you are seeing too many locks then you have to think about doing the updates/inserts in batches instead of one big change.Look at the isolation levels also. The default is read committed and higher the isolation level, lower is the concurrency.
Example of Simple Locking :
User 1(U1) Starts Transaction , User 2 (U2) Starts Transaction , U1 Updates row in a table T1 , where as U2 attempts to update row in table T1 , U2 is blocked by U1 action. Now U1 Commits transaction and U2 updates the transaction , since U1 has made some changes to table T1 (Dirty Read) U2 is not honored the lock (data that is modified on Table T1), U2 Rolls back.
I have used nolock and am using nolocks as I work on large databases with high traffic systems but not in every place and every statement. This is ok as long as you know the consequences and DO NOT use in banking related transactions.You can't simply clear all locks from a table. Locks are held for a reason to maintain the ACID (Atomicity, Consistency, Isolation, and Durability)
ACID Properties of Transactions
Atomicity : Atomicity means all or nothing. Transactions often contain multiple separate actions.
Consistency : Consistency means that transactions always take the database from one consistent state to another
Isolation : Isolation means that concurrent transactions, and the changes made within them, are not visible to each other until they complete.
Durability : Durability means that committed transactions will not be lost, even in the event of abnormal termination.
If you are seeing too many locks then you have to think about doing the updates/inserts in batches instead of one big change.Look at the isolation levels also. The default is read committed and higher the isolation level, lower is the concurrency.
Example of Simple Locking :
User 1(U1) Starts Transaction , User 2 (U2) Starts Transaction , U1 Updates row in a table T1 , where as U2 attempts to update row in table T1 , U2 is blocked by U1 action. Now U1 Commits transaction and U2 updates the transaction , since U1 has made some changes to table T1 (Dirty Read) U2 is not honored the lock (data that is modified on Table T1), U2 Rolls back.
Categories:
Database
0 comments:
Post a Comment