We have hit deadlocks in 10.1.25-MariaDB-1~trusty. Looks like it has something to do with select for update queries. But the deadlock increases when number of tps increases. Is there any alternative to fix this from DB end or from application end to change the logic?
1 Answer
A deadlock is effectively two concurrency transactions updating the same data. This need to be handled at application level because there's no obvious solution that always works without accounting for the business case.
SELECT FOR UPDATEqueries, their table, indexes and query plan (EXPLAIN) andSHOW ENGINE INNODB STATUScould help. Fixing the indexes could make the queries faster, but this will only make the deadlock occurrence less likely. There's a possibility that excessive rows where locked here.