Solved

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

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server - problem connecting to database engine from management studio 4 38
TSQL query to generate xml 4 31
SQL Improvement  ( Speed) 14 26
Tsql query 6 20
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

785 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