Locks, blocks and deadlocks - what's the difference?
We are often being paged by development teams talking about locks, blocks or deadlocks and some people make the wrong use of the terms. There is a big difference between the three and it will explained at a high level in this post: Lock Lock is acquired when any process accesses a piece of data where there is a chance that another concurrent process will need this piece of data as well at the same time. By locking the piece of data we ensure that we are able to action on that data the way we expect. For example, if we read the data, we usually like to ensure that we read the latest data. If we update the data, we need to ensure no other process is updating it at the same time, etc. Locking is the mechanism that SQL Server uses in order to protect data integrity during transactions. Block Block (or blocking lock) occurs when two processes need access to same piece of data concurrently so one process locks the data and the other one needs to wait for the other one to complete and release the lock. As soon as the first process is complete, the blocked process resumes operation. The blocking chain is like a queue: once the blocking process is complete, the next processes can continue. In a normal server environment, infrequent blocking locks are acceptable. But if blocking locks are common (rather than infrequent), there is probably some kind of design or query implementation problem and the blocking may simply be causing performance issues. A block can be described like this:
![Block](https://static.hsstatic.net/BlogImporterAssetsUI/ex/missing-image.png)
![Deadlock](https://static.hsstatic.net/BlogImporterAssetsUI/ex/missing-image.png)
- Use clustered indexes on high-usage tables.
- Avoid high row count SQL statements that can cause a table lock. For example, instead of inserting all rows from one table to another all at once, put a single INSERT statement in a loop and insert one row at a time.
- Break long transactions up into many shorter transactions. With SQL Server, you can use "bound connections" to control the execution sequence of the shorter transactions.
- Make sure that UPDATE and DELETE statements use an existing index.
- If you use nested transactions, be sure there are no commit or rollback conflicts.
- Access objects always in the same order (i.e.: update Table1, Table2 and Table3 rather than sometimes Table2 first).
- Don’t schedule long data updating processes to run concurrently, if possible.
- Keep transactions as short as possible.
Share this
You May Also Like
These Related Stories
Infographic: What is Data Governance?
![](https://www.pythian.com/hubfs/Imported_Blog_Media/Data-Governance-Social.jpg)
Infographic: What is Data Governance?
Mar 25, 2020
1
min read
What You Need to Know About Oracle EBS 12.2 CPU – January 2023
![](https://www.pythian.com/hubfs/Imported_Blog_Media/What-You-Need-to-Know-About-Oracle-EBS-12_2.jpg)
What You Need to Know About Oracle EBS 12.2 CPU – January 2023
Feb 28, 2023
2
min read
Cloud automation is winning. Will your organization share in the prize?
![](https://www.pythian.com/hubfs/Imported_Blog_Media/Cloud-automation2Fwin.png)
Cloud automation is winning. Will your organization share in the prize?
Jul 12, 2018
3
min read
No Comments Yet
Let us know what you think