Mysql 5.7: Dead Lock Issue SQLSTATE[40001]

[Irfan Ahamd]

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
;