i keep getting
caused by: java.sql.sqlexception: transaction (process id 61) deadlocked on lock resources process , has been chosen deadlock victim. rerun transaction. so turned on mssql logging using
dbcc traceon(1222,-1) dbcc traceon(1204,-1) and following deadlock info printed in logs:
these line indicate deadlock has been detected, , process 1 has been chosen victim.
process-list deadlock victim=process_id_1 deadlock-list logs of process_id_1
[1] (@p0 nvarchar(4000))select namesequen0_.id id1_47_<c/> namesequen0_.container_id containe9_47_<c/> namesequen0_.increment_ incremen2_47_<c/> namesequen0_.nextvalue nextvalu3_47_<c/> namesequen0_.numchars numchars4_47_<c/> namesequen0_.padchar padchar5_47_<c/> namesequen0_.scope scope6_47_<c/> namesequen0_.sequencetype sequence7_47_<c/> namesequen0_.uppercase uppercas8_47_ namesequence namesequen0_ (namesequen0_.scope @p0 ) , (namesequen0_.container_id null) [2] frame procname=adhoc line=1 stmtstart=40 sqlhandle=0x020000005734e00acfb3060d49cc0e8565acb3b105807744 [3] executionstack [4] process id=process_id_1 taskpriority=0 logused=1248 waitresource=key: 14:72057594053459968 (95c9fddfaf17) waittime=3506 ownerid=13645568 transactionname=implicit_transaction lasttranstarted=2015-06-02t07:52:41.660 xdes=0xa30dd950 lockmode=s schedulerid=1 kpid=1848 status=suspended spid=68 sbid=0 ecid=0 priority=0 trancount=1 lastbatchstarted=2015-06-02t07:52:41.663 lastbatchcompleted=2015-06-02t07:52:41.660 clientapp=jtds hostname=qalab6 hostpid=123 loginname=qalab6_nrm isolationlevel=read committed (2) xactid=13645568 currentdb=14 locktimeout=4294967295 clientoption1=671088672 clientoption2=128058 logs of process_id_2
[1]select card0_.id id2_77_<c/> card0_.autodisplayname autodisp3_77_<c/> card0_.autopartialname autopart4_77_<c/> card0_.namedwithsequence_id namedwi38_77_<c/> card0_.namedwithsequencevalue namedwit5_77_<c/> card0_.userdisplayname userdisp6_77_<c/> card0_.userpartialname userpart7_77_<c/> card0_.lifecyclestate_id lifecyc40_77_<c/> card0_.model_id model41_77_<c/> card0_.outofservice outofse12_77_<c/> card0_.bottomclearance bottomc13_77_<c/> card0_.leftclearance leftcle14_77_<c/> card0_.rightclearance rightcl15_77_<c/> card0_.topclearance topclea16_77_<c/> card0_.ncmelementkey ncmelem24_77_ vfditem card0_ card0_.dtype='card' , card0_.ncmelementkey= @p0 [2]frame procname=adhoc line=1 stmtstart=40 sqlhandle=0x02000000386ba721896ce39b0b7c9d01df11539c0d843c83 [3]executionstack [4]process id=process_id_2 taskpriority=0 logused=62048 waitresource=key: 14:72057594051297280 (fadae9b0c9d3) waittime=3333 ownerid=13645330 transactionname=implicit_transaction lasttranstarted=2015-06-02t07:52:39.570 xdes=0xbf9f5950 lockmode=s schedulerid=1 kpid=5344 status=suspended spid=66 sbid=0 ecid=0 priority=0 trancount=1 lastbatchstarted=2015-06-02t07:52:41.833 lastbatchcompleted=2015-06-02t07:52:41.813 clientapp=jtds hostname=qalab6 hostpid=123 loginname=qalab6_nrm isolationlevel=read committed (2) xactid=13645330 currentdb=14 locktimeout=4294967295 clientoption1=671088672 clientoption2=128058 now following line gives details of process_id_1,
ref[1] query want execute...note: select query on table name namesequence , namesequence0_.container_id foreign key table name vfditem.
ref[4] says waiting resource , suspended
now following line give details of process_id_2 in deadlock process_id_1
ref[1] give query process_id_2 want run...note select query on table name vfditem , card0_.namedwithsequence_id foreign key table name namesequence
ref[4] says waiting resource , suspended
now following lines prints resource locked , whom , waiting resource.
[11] waiter id=process_id_2 mode=s requesttype=wait [10] waiter-list [9] owner id=process_id_1 mode=x [8] owner-list [7] keylock hobtid=72057594051297280 dbid=14 objectname=qalab6_nrm.dbo.vfditem indexname=vfditem_pk id=lock85330d00 mode=x associatedobjectid=72057594051297280 [6] waiter id=process_id_1 mode=s requesttype=wait [5] waiter-list [4] owner id=process_id_2 mode=x [3] owner-list [2] keylock hobtid=72057594053459968 dbid=14 objectname=qalab6_nrm.dbo.namesequence indexname=namesequence_pk id=lock848c3380 mode=x associatedobjectid=72057594053459968 [1] resource-list this translates
[1] resource qalab6_nrm.dbo.namesequence indexname=namesequence_pk locked process_id_2 in exclusive lock mode , process_id_1 waiting on it.
[2] resource qalab6_nrm.dbo.vfditem indexname=vfditem_pk locked process_id_1 in exclusive lock mode , process_id_2 waiting on it.
bingo can see how been deadlocked
now weird part
if closely process_id_1 trying run select query table name namesequence , column namesequence.container_id foreign key table vfditem , process_id_1 has acquired lock on indexname=vfditem_pk of vfditem table
[1] why process_is_1 acquire lock on index of vfditem ?
further process_id_2 trying run select query table name vfditem , column vfditem.namesequence_id foreign key table namesequence , process_id_2 has acquired lock on indexname=namesequence_pk of namesequence table
[2] why process_is_2 acquire lock on index of namesequence?
mssql uses locking on select statements, can table, page , row locks. depends on want in system , more important pointer along way @ table hints can put on select statement, looking @ nowait, nolock, readcommitted , read_committed_snapshot
you may want consider setting isolation level on of transactions avoid issue in other places.
this pretty basic information , dba may able provide more detailed answer, these options prevent issue happening
you can tell isolation level running dbcc useroptions , can tell read_committed_snapshot running select is_read_committed_snapshot_on sys.databases name= 'yourdatabase'
Comments
Post a Comment