CODEWITHSUNDEEP
mysqlisolation-leveldatabase-deadlocks
hsnsd
hsnsd

Reputation: 1823

MySQL transactions in a stored procedure

I have a stored procedure which collects data from several sources, transforms and in the end deletes and then dumps the data in TableA While this stored procedure is running, other users often perform a select query on TableA resulting in a deadlock, causing the stored procedure to fail.

My stored procedure does use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; at the beginning however it does not seem to work or I am using it wrongly.

Without using READ/WRITE LOCKS is there a way I can ensure that this does not happen?

MySQL version: 5.7.23-enterprise-commercial-advanced-log

Upvotes: 0

Views: 1545

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562320

I have a number of comments:

  • I assume you're using SELECT ... FOR UPDATE because without the optional locking clause, SELECT does not acquire row locks, so should not cause a deadlock.

  • Other read queries without the FOR UPDATE clause don't need row locks. They cannot be contributing to the deadlocks.

  • To prevent deadlocks, you need to use an atomic lock. Consider LOCK TABLES.

  • Changing the isolation level has no effect on the currently running transaction. If you already have a transaction in progress, you cannot change its isolation level on the fly. The change to the isolation level only applies to transactions you begin subsequently.

  • The READ UNCOMMITTED isolation level does not eliminate the need for queries to acquire locks, if they need locks in other isolation levels.

  • In 20+ years of using MySQL, I've never found a legitimate use for READ UNCOMMITTED. I avoid it.

Upvotes: 2

Related Questions