Mysql 5.7: Dead Lock Issue SQLSTATE[40001]
We are running DB-SP on need basis and this store procedure run successfully but sometime its through deadlock exception
SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction (SQL: CALL my sp name
1) I deployed following line on the beginning of SP
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
2) This SP have more than 5000 rows and i pin point the issue on particular command which actually generate deadlock. What you suggest against this command to optimize and never generate deadlock. I would really appreciate your thoughts.
UPDATE DDDDD d
INNER JOIN(
SELECT id
FROM(
SELECT d.id
FROM DDDDD d FORCE INDEX(index_2)
INNER JOIN tmp_ttt_tab1 tt
ON tt.proxy_card_cl_details_id=d.proxy_card_cl_details_id
WHERE d.status_id=2
AND d.status='unassigned'
AND d.is_deleted=0
AND d.is_active=1
ORDER BY d.last_used_at ASC
LIMIT v_total_rows
)t
)t ON t.id=d.id
SET d.pass_id=0
, d.log_id=p_log_id
, d.status='assigned'
, d.status_id=3
, d.updated_at=UTC_TIMESTAMP()
, d.last_used_at=UTC_TIMESTAMP()
, d.updated_by=p_created_by
;
Subject
Written By
Posted
Mysql 5.7: Dead Lock Issue SQLSTATE[40001]
November 02, 2019 09:11AM
November 02, 2019 11:04AM
November 04, 2019 01:11AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.