Solved

Large number of lock with one of the session id in sql2005

Posted on 2013-05-17
2
234 Views
Last Modified: 2013-05-22
I just trying to understands why one of the sessions could place around 700 000 lock ? I found this with a command below.

actually when is sql server placing a lo of locks compare to only 1 lock ? for example if we update one table with 1 millions of rows. Is this produced one or 1millions of locks ?

update tableB set columnB='Testing123"

SELECT request_session_id, COUNT (*) num_locks
FROM sys.dm_tran_locks
GROUP BY request_session_id
ORDER BY count (*) DESC
0
Comment
Question by:motioneye
2 Comments
 
LVL 12

Assisted Solution

by:sachitjain
sachitjain earned 250 total points
ID: 39178118
Looks like you are not updating records in transaction block. Can you try this?
BEGIN TRY
      BEGIN TRANSACTION
            update tableB set columnB='Testing123'
            COMMIT
END TRY
BEGIN CATCH
      ROLLBACK
END CATCH
0
 
LVL 25

Accepted Solution

by:
TempDBA earned 250 total points
ID: 39180088
SQL server takes care of locking itself for update statement which can be a row, page or table lock. Once update is committed, it will release the lock.
To avoid row lock on all the rows, you can do batch updation.

Ref:http://msdn.microsoft.com/en-us/library/aa213039%28v=sql.80%29.aspx
http://stackoverflow.com/questions/3114826/is-it-possible-to-force-row-level-locking-in-sql-server
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now