Database Locks

Question

Database Locks

CrazyMan

SSChampion

Points: 12560
More actions
February 13, 2008 at 7:21 am

#124014

Hi All
I am facing an issue with database updates, i got a stored procedure which updates a table, this process takes about a minute to process, by this time the table is been locked by this SP, other users cant use this to select or to insert a particular record, i am using SQL 2005, is there any ways to overcome this situation, if so how can i do it?? please let me know
Cheers
Suji

Viewing 15 posts - 1 through 15 (of 23 total)

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

KenpoDBA SSCrazy Eights Points: 9811 More actions · Answer 1

Yeah, in order to have selects blaze through your update statements, you need to either use with (nolock), or with (readpast). If you're running several of these updates at once though, it's best to run them with (updlock) so they won't block each other. Inserts require exclusive locks on their rows though, so they'll just have to be blocked while the update is going on if they're trying to access the same rows.

Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant

Minion Maintenance is FREE:

free_mascot One Orange Chip Points: 27169 More actions · Answer 2

Check the new feature of SQL Server Snapshot Isolation; if that can help.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

KenpoDBA SSCrazy Eights Points: 9811 More actions · Answer 3

Isolation can help, but you can also cause more problems by putting that stress in tempdb. So just be careful with it because unless you've designed for it, you could be in trouble.

Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant

Minion Maintenance is FREE:

lucky-80472 SSCertifiable Points: 7793 More actions · Answer 4

Suji (2/13/2008)
Hi All
I am facing an issue with database updates, i got a stored procedure which updates a table, this process takes about a minute to process, by this time the table is been locked by this SP, other users cant use this to select or to insert a particular record, i am using SQL 2005, is there any ways to overcome this situation, if so how can i do it?? please let me know
Cheers
Suji

Hello Suji,

This is true that SQL Server applies exclusive lock on the table when any update statement is given without a filter condition. You should be passing some input value to the stored procedure so that when the underlying update statement is executed it only locks the set of rows which are needed.

Hope this helps.

Thanks

Lucky

Tommy Bollhofer SSChampion Points: 14940 More actions · Answer 5

Have you tried a ROWLOCK hint in your update?

Tommy

Follow @sqlscribe

CrazyMan SSChampion Points: 12560 More actions · Answer 6

Hi Cever

Updlock is not working, i cant also use snapshot isloation since there is not enough space, is there any othere way to over come this problem??

Thanks

Sujith

CrazyMan SSChampion Points: 12560 More actions · Answer 7

CrazyMan

SSChampion

Points: 12560

February 13, 2008 at 8:31 am

#777873

Hi Crever

Thanks for the reply

Thanks

Sujith

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 8

Can't you go with the readcommited isolation level. Did you find out what is the level of lock being held. Also try using READPAST hint in the query. so that you are never a cause of blocking.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

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

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 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

CrazyMan SSChampion Points: 12560 More actions · Answer 10

Hi Sugesh

Thanks mate, but my DB's are on read committed isloation level, but it still blocks, i dont know what to do??

Cheers

Sujith

CrazyMan SSChampion Points: 12560 More actions · Answer 11

Hi Gail

The update statement checks some table in a remote server using linked servers and then updates the table, this process takes 1 minute, we use threads iin java applications, so there are hell a lot number of transcations going on per second ( Java application only inserte into the table)

Thanks

Sujith

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

Can you post the code, the schema and indexes on the table and an approx row count please?

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

Animal Magic SSChampion Points: 13964 More actions · Answer 13

Follow Gails lead for now as it seems your SP can probably be optimised, but one thing maybe worth considering is bring the data from the remote server into a staging/holding table first and then run the SP from local data. If its the fact the server is remote that's causing most of the slowdown then this solution will overcome that.

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

Absolutely. While it wouldn't speed up the entire process, it would shorten the length of the locks on the updated table.

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