Solved

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

Posted on 2013-05-17
2
246 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

734 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