Solved

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

Posted on 2013-05-17
2
251 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

636 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