December 10, 2007 at 2:56 am
Hi People,
My doubt is:
With locks we make sure that we lock a particular row/ page/ table / database / or not lock any of these while we are make changes or reading data from database
With Isolation we make sure that we are reading appropriate data in terms of in case of any insert/ modification is made by any other transaction. Various Isolation levels are realized by Locks ....
my doubt being that if we can use Isolation levels why do we need to use Locks ???
As i also read that locks can be upgraded as in while issuing an Update statement if the update occurs on number of rows then a page level lock is issued ....
Thanks
December 10, 2007 at 7:02 am
You have to start from a concurrency model. It's either pessimistic or optimistic. You seem to be assuming an optimistic model, there are unlikely to be very many or any conflicts arising from reads & writes, therefore locking is a redundancy that just slows down the system. Unfortunately a lot of us operate in the pessimistic universe where multiple systems are hitting the databases through different methods and access paths. Here the likelihood of an issue due to conflict is higher. Locks become a vital protection mechanism within the system.
Just an aside, according to what I understand, Isolation levels set locking behavior. You don't get one without the other. Maybe I'm misunderstanding your question at the beginning?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 11, 2007 at 6:04 am
All systems that allow multiple update access to the same pool of data and implement transaction integrity use locks. The physical implementation will be different according to vendor, but they all use locks.
If you do not have a lock, you do not have a way to enforce transaction integrity. Without transaction integrity, your data gets corrupted.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
December 11, 2007 at 6:54 am
I do understand what u guys said but if we do have isolation levels then why do i need locks? as in ... If i can accomplish the transaction integrity by mentioning appropriate Isolation levels .... then why would i mention ROWLOCK, TABLOCK, UPDLOCK
December 11, 2007 at 6:58 am
You do not personally have to request the locks. SQL Server does this for you automatically.
It is best wherever possible to let SQL Server manage all the locking, or you are likely to keep locks for longer than necessary.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
December 11, 2007 at 7:01 am
You only want to put that stuff in as a last resort, usually because of some sort of design error, bad index, or something else that you can't spend time fixing. As a general rule, don't use them.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply