deadlock issue
Ranusmita Panda
I am facing an issue in deadlock.My tables are row lock
1:00000:00283:2011/06/10 18:14:25.34 server Deadlock Id 65 detected
Deadlock Id 65: detected. 1 deadlock chain(s) involved.
Deadlock Id 65: Process (Familyid 0, Spid 889, Suid 3) was executing a
UPDATE command in the procedure 'proc_risk_pos_summ_40' at line 1104
at nested level 2.
Deadlock Id 65: Process 889 was involved in application 'ACTUATE'.
Deadlock Id 65: Process 889 was involved on host name 'srxsinaaauat2'.
Deadlock Id 65: Process 889 was involved in transaction
'$user_transaction'.
SQL Text: æ
Executing procedure: proc_test_1
Subordinate SQL Text: UPDATE table1 set tot_lv_inactv = (select
ISNULL(sum(pos.loanable_value),0)
FROM table2 pos
WHERE pos.r_dataset_id = @dataset_id1
and (CASE WHEN rps.portfolio=rps.master_code THEN master_code ELSE
account_code END) = rps.portfolio)
FROM table1 rps
WHERE r_dataset_id = @dataset_id1
Deadlock Id 65: Process (Familyid 0, Spid 283, Suid 3) was executing a
UPDATE command in the procedure 'proc_risk_pos_summ_40' at line 783 at
nested level 2.
Deadlock Id 65: Process 283 was involved in application 'ACTUATE'.
Deadlock Id 65: Process 283 was involved on host name 'srxsinaaauat2'.
Deadlock Id 65: Process 283 was involved in transaction
'$user_transaction'.
SQL Text: nh_flag=0 and convert(varchar,effective_date,112)<=
@rep_date)
Executing procedure: proc_test_1
Subordinate SQL Text: update table2 set ltv_enhanced = CASE WHEN
st_exclude_f = 1 THEN fr_fin_ratio
ELSE (CASE WHEN fr_fin_ratio IN (0,100) THEN
fr_fin_ratio
ELSE convert(numeric(38,9),
95 - convert(numeric(38,9),convert(numeric(38,9),(100-
fr_fin_ratio-5))*
convert(numeric(38,9),(100-(select distinct stress_test from
table1 where master_code = rcp.master_code and r_dataset_id =
@dataset_id1))/100))
) END) END
from table2 rcp
where r_dataset_id=@dataset_id1
AND rcp.account_code||rcp.instr_code not in (select distinct
IsNull(portfolio_code||instr_code," ") from table3 where
ltv_enh_flag=0 and convert(varchar,effective_date,112)<=
@rep_date)
Deadlock Id 65: Process (Familyid 0, Spid 283) was waiting for a
'shared page' lock on page 69745 of the 'table1' table in database
'aaarepdb' but process (Familyid 0, Spid 889) already held a
'exclusive page' lock on it.
Deadlock Id 65: Process (Familyid 0, Spid 889) was waiting for a
'shared page' lock on page 72955 of the 'table2' table in database
'aaarepdb' but process (Familyid 0, Spid 283) already held a
'exclusive page' lock on it.
Deadlock Id 65: Process (Familyid 0, Spid 889) was chosen as the
victim.
Victim process host = `srxsinaaauat2', user = `aaa' program name =
`ACTUATE' host processes = `21623' .
I am apllying explicit begin tran and commit tran but still getting
deadlock.can anyone help how to resolve this
Begin tran
UPDATE Table1 set tot_lv_inactv = (select
ISNULL(sum(pos.loanable_value),0)
FROM table2 pos
WHERE pos.r_dataset_id = @dataset_id1 and (CASE WHEN
rps.portfolio=rps.master_code THEN master_code ELSE account_code END)
= rps.portfolio)
FROM Table1 rps
WHERE r_dataset_id = @dataset_id1
Begin Tran
Commit
END