What are Locks?

Locks are used in SQL Server for transaction isolation to ensure that transactions do not interfere with each other. If we look at the example above if both Transaction 1 and Transaction 2 were to operate on the same record at the same time, they would both be operating on the initial balance of $500.00. This would lead to inconsistent data, where Transaction 1 would leave $200 in the account and Transaction 2 would leave $100 in the account. The bank would have subtracted $700 from a $500 balance and left $100 in the account. (This specific example is considered a Lost Update.)

To make sure this does not happen in a database, when Transaction 1 initially runs it will place a Lock on the record forcing Transaction 2 to wait until the first Transaction has finished. After Transaction 1 has been completed, the second Transaction would then read that only $200 is actually left in the account. This would then allow the ability to either have the second Transaction print a message that there are insufficient funds and rollback the transaction or continue to process the Transaction which would leave -200 in the account. You can set different locking settings by changing the Transaction Isolation Levels which is set to READ COMMITTED by default.

Be the first to comment on "What are Locks?"

Leave a comment

Your email address will not be published.


This site uses Akismet to reduce spam. Learn how your comment data is processed.