Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2013-05-17
2
Medium Priority
?
253 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 1000 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 1000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
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.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

715 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