Stored procedure SQL Server 2005 transaction deadlock problem -


i have problem while running sql stored procedure (mssql 2005). here code of it:

create procedure [getitemcolors]     @fetchcount int begin select      top (@fetchcount) item.id itemid, item.published published, attributevalue.string color     tblitem item, tblattribute attributevalue, tblattributedefinition attributedef              item.id = attributevalue.fkitemid          , item.deleted = 0         , item.pendingpublish = 0          , attributevalue.fkattributedefinitionid = attributedef.id         , attributedef.[name] = 'color'     order item.published desc end; 

some times execution of procedure ends exception:

sqlexception: transaction (process id 66) deadlocked on lock | communication buffer resources process , has been chosen deadlock victim. rerun transaction. 

does know how rewrite procedure avoid exception?

is there anyway of 'pre-locking' tables within stored procedure invocation other code can't interrupt execution?

thanks in advance help!


edit 1: estimated execution plan attached

estimated execution plan


edit 2: can't deadlock graph exception happens couple times per day in live environment only. i'm not allowed run profiler in live environment. didn't manage reproduce deadlock in development environment.


edit 3: didn't managed deadlock winner.

  1. post deadlock info. see how to: save deadlock graphs.
  2. post statement procedure deadlocks with (ie. deadlock winner)
  3. post exact schema of tables involved, including indexes, clustered , non-clustered

you execution plan has no less 3 clustered index scans. picture of plan instead of actual plan 1 cannot actual properties of operators, lines quite thick (indicating large results) , behind parallelism operators. these point toward bad indexing strategy causes table scans. such large scans guaranteed conflict any update. when conflict results in deadlock (which happens due index update order) query, read-only operation, chosen ads victim.

one solution index database no longer have expensive table scans. besides overall performance benefit, reduce probability of deadlock conflict.

if deadlock persist, answer specific depending on actual deadlock situation.

as magic 'pre-lock' strategy, can take sledgehammer , decorate query or tablock or tablockx hints, performance go down drain in no time. proper solution address deadlock, not force serialization.


Comments

Popular posts from this blog

apache - Add omitted ? to URLs -

redirect - bbPress Forum - rewrite to wwww.mysite prohibits login -

php - How can I stop spam on my custom forum/blog? -