Avoid Deadlock while Update 2 rows in MySQL

declare @transactionId int = 0, @sourceId int = 0, @documentId int;

insert into [transaction] (transaction_date) values (CURRENT_TIMESTAMP);
select @transactionId = SCOPE_IDENTITY();
if @processName = 'process_a'

BEGIN
update top (1) document set
is_checked_out = 1,
process_a_status = 'P',
transaction_id = @transactionId
where
is_checked_out = 0 and
process_a_status = 'Y' and
session_id = @sessionId
select @documentId = document_id, @sourceId = source_id from
document where transaction_id = @transactionId;
END

else if @processName = 'process_b'

BEGIN
update top (1) document set
is_checked_out = 1,
process_b_status = 'P',
transaction_id = @transactionId
where
is_checked_out = 0 and
process_b_status = 'Y' and
session_id = @sessionId
select @documentId = document_id, @sourceId = source_id from

document where transaction_id = @transactionId;
END

Tagged in:

• deadlock
• Mysql