SQL Server Performance Forum – Threads Archive

Blocking

i have a problem here currently
couple of process is blocked by othere couple
I am killing blocking process and than an other one is causing blo cking
THose statem ensts are insert and delet e to the same table
What should i do?

Refer thru this linkhttp://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=2158 where blocking issues are dealt. And this webcasthttp://support.microsoft.com/default.aspx?scid=/servicedesks/webcasts/wc011502/wcblurb011502.asp for inside blocking concept. HTH
_________
Satya SKJ
Moderator
SQL-Server-Performance.Com

this is current value of the lock on this server: locks5000214748364700

?? where are the values? _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

sorry:
locks5000214748364700

Current configured are 5000, try to set around 50000 and see the results.
Ensure this requires restart of SQL services to take affect. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

5000 is minimum value of the configuration option
maximum is 2147483647 so changeing min will help?

Yes the blocking will occur due to minimum no.of locks configured and it helps to increase to 50000 or 25000 in your case. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

Must come back to this post just in case somebody don’t do same mistake as i did. I run sp_configure wich returned me this :
name minimum maximum config_value run_value
———————————– ———– ———– ———— ———– locks 5000 2147483647 0 0
I thought 5000 is min value with SQL consider reporting blocking but actually it’s run_value that comand to SQL server what min locks should be reported as blocking. So thingking it’s 5000 i run this sp_configure ‘locks’,’20000′ Which actualy reduced the nuber of allowed locks because 0 stands for indefined amount of locks.
So run_value is actual configured lock info you will be looking for.

SO the blocking is reduced now or not? _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

Blocking now resulting in deadlock
I have delete statement which produce X lock and then insert
statement with IX those causing deadlock.
What if i ask developers set IX mode on delete stetement using command object. IX and IX are compatible Is that going to work?

hello everyone,i’m a new sql dba here <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> i change the locks config on my sql server today from the default setting 0 to 50000, running that command sp_config ‘locks’,’50000′ name minimun maximum config_value run_value locks500021474836475000050000 so it would be better to leave it like this, or it would run better with the default 0???

change it back emidiately

i was actually running an update on one of my tables,and i got a this error 1204 severity 19 state 1, and then can not obtain a lock or something. so, done, i went back to default settings

NewDBA – There are a few things I can think about without looking at the code. Make sure the transaction isolation level is the lowest you can use. don’t use repeatable read if you can do with read committed. keep your transaction as short as possible. make sure your table is well indexed so the where clause is resolved as soon as possible.
Try using update lock as it will serialize access to the table and will minimize lock problems. I suspect your deadlocks are conversion deadlock and not cycle deadlocks and in those cases using update lock can make a big difference. You can also try to use rowlock (or paglock) hint.

]]>