Dead Lock victims happen while inserting data but the victim comes from Select query from another process

Question

Dead Lock victims happen while inserting data but the victim comes from Select query from another process

Saravanan_tvr

SSCarpal Tunnel

Points: 4558
More actions
August 10, 2012 at 2:56 am

#264956

Here is the problem:
I just inserting tables
Procedure Body is
(
Isolation level is Read committed
Begin transactions
insert statements (Table1(Id1 ,ID2) ) ...
insert statements (Table2(Id21,ID22) ) ...
commit transactions
if @@error <> 0
Rollback
)
--------------------------Info
From the above(Table1) ID1 as Primary Key
From the above(Table2) ID2 as Foreign key with X table reference
Finally the Non clustered index Table1 as IDX_ID1_ID2
While the executing the above procedures
--------------------------------Info
From another SPID
Just using those table in select only
Isolation level is Read committed
select * from
X
join
Table1
Join Table2
join table 3 etc...
-----------------------------------------------
But the SQl Deadlock log says the below error
(Just select Query) Request Mode RangsS <----> Key Lock(Index Name :IDX_ID1_ID2 ) ------->Owner Mode X (Sp is running)
Please let me know someone how the select orations make as Dead Lock,

Viewing 6 posts - 1 through 5 (of 5 total)

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

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 1

You've got the isolation level set to READ COMMITTED in your second (SELECT) transactions. So if they are trying to read data from tables locked for UPDATE (your first transaction), locking will result.

Either move the schedules of your jobs so they don't clash, set the isolation level to READ UNCOMMITTED, or make other amendments to prevent the collisions.

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

Saravanan_tvr SSCarpal Tunnel Points: 4558 More actions · Answer 2

But its Blocking issue only right? The select statement will be in wait status until the insertion Procedures commit or Rollback

Then why this state is moving to DEAD Lock victims?

Ant-Green SSC Guru Points: 113507 More actions · Answer 3

From the description you have given yes it should only be a blocking issue, so either your not seeing the full picture or something else is happening.

Do you have the full deadlock graph for the deadlock? Trace flag 1222, -1 applied?

Also your error handling in the first proc is wrong, also lookup try catch blocks instead of checking @@error

You would check @@error before commiting

insert ... into ...

if @@error <> 0

begin

rollback tran

end

else

begin

commit tran

end

Saravanan_tvr SSCarpal Tunnel Points: 4558 More actions · Answer 4

Thanks I agree, its Blocking issues, Now I would like to know with one scenario;

I have Insertions statement to table (T1) in one SP;

that same Sp getting execution at the same time with different SPID's,, so while what will happen , Like Dead lock else Etc... ?

If deadlock mean what should i follow to rectify...

Thanks in advance.

Ant-Green SSC Guru Points: 113507 More actions · Answer 5

I suggest reading up on deadlock management.

Use the link in my signature to the Guide for the Accidental DBA and take a look in the handling deadlocks chapter