Database Locks

Question

Database Locks

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply

CrazyMan SSChampion Points: 12560 More actions · Answer 1

Hi Gail

Thanks a lot for the reply, i have made some changes and it dint work on Stored procedure, but when i made the same job as a T-SQL statement there was no locking issues, its sorted out now,

I am still wondering why it creates a locking issues when i run a job with Stored procedure and no issues were found when i do the same with T-SQL

Thanks a lot once again for all for helping me to solve this issue

Cheers

Suji

Preethiviraj Kulasingham Mr or Mrs. 500 Points: 583 More actions · Answer 2

Hi,

Read Commited is the default isolation of the database.

can you Identify all the rows and update tem in small chuncks so that the locks will not be at the table level but at the page level?

Cheers,
Prithiviraj Kulasingham

Plan to Test your Plan!

Anders Pedersen SSChampion Points: 11410 More actions · Answer 3

I have had situations where I had to compare one table with the same table remotely to do updates. The only way I could do this without causing serious locking issues was to download the table localy first. Cut the time I was locking the table by a factor of 1000. Cut it by another factor of 100 by making sure indexes where optimized.

Ignacio A. Salom Rangel SSC-Insane Points: 20443 More actions · Answer 4

GilaMonster (2/13/2008)
What does the update statement do?
Can it be optimised to take less time? Can the indexes be tweaked so that it doesn't have to lock the entire table?

Gail what do you mean by tweaking the indexes?

My blog

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

1) the first reponse to this type of question is we need to see the code and table scripts.

2) how many rows are being updated by the code?

3) is indexing appropriate? I am betting not

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Ignacio A. Salom Rangel (3/30/2008)
GilaMonster (2/13/2008)
What does the update statement do?
Can it be optimised to take less time? Can the indexes be tweaked so that it doesn't have to lock the entire table?
Gail what do you mean by tweaking the indexes?

Add or remove columns to indexes, add new indexes, change the order of columns within the index.

Basically anything to make SQL use the index better.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ignacio A. Salom Rangel SSC-Insane Points: 20443 More actions · Answer 7

Ignacio A. Salom Rangel

SSC-Insane

Points: 20443

March 31, 2008 at 7:50 am

#795944

Thanks for the tip!

My blog

gkarung SSC Enthusiast Points: 105 More actions · Answer 8

Hi

How many types of LOCKS in sql server 2005 and which is default lock?

Thanks & Regards

G Arunagiri

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 9

This is WAYYYY to open-ended a question. See here in BOL: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/5530f484-5bc4-467c-ae50-d708dafb24d0.htm for the starting topic on SQL Server locking. There also is no default type of lock. Locks are taking by the engine depending on a very large number of items/settings.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service