Multiple rows locked in sqlserver when updating one row

5 pts.
Tags:
SQL Server
SQL Server 2005
SQL Server 2008
In Microsoft SQLServer, I found out that when you perform an SQL update, in a lot of cases multiple rows are locked, even when row-locking is configured for that table and page-locking is prohibited, and even when you force row-locking in the SQL statement using WITH (ROWLOCK).

Software/Hardware used:
SQLServer 2005, SQLServer 2008
ASKED: March 9, 2010  9:03 AM
UPDATED: March 9, 2010  7:44 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

You didn’t give any example of the code that is causing this behavior. SQL server can choose to escalate locks from row to page or even table locking, depending on the queries and transactions. (So will any database system.)

An update statement that doesn’t use the primary key, for example, will usually cause lock escalation. Also, if you include “Select” statements in a transaction and those do not use the “(nolock)” hint, you will frequently see locks escalate up to the table level.

To minimize lock escalations, try the following:
1. Ensure all Update (or Delete) statements only use the primary key to specify the records to be affected.
2. Keep transactions as short as possible. Avoid loops or multi-row updates or deletes within a single transaction. If your number-one concern is locking and not performance, it is better to do updates and deletes as single-row transactions, with a “commit” after each row.
3. Where possible, add the (nolock) hint to your select statements. Note that you need to understand the possible side effects before doing this to lots of statements, and you may want to specify a different transaction isolation level in different transactions. (Account ledger updates probably need to be serializable, for example.) Don’t go too crazy on this, but there are usually a lot of places where (nolock) can be used safely, and these will definitely reduce lock contention.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following