0

I have a query that I run once a day that takes certain tables from my database and puts them into a single table so that I can quickly export the information in any format I require.

I am running into a problem however that gives me the following error: "SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction"

From what I understand it sounds like my query is trying to get a lock on the table when there already is a lock in place. However I confess that I do not know anything about table locking or how it works.

Really I am just trying to read the other tables and not write to them, is there a way that I can create the query but not request a lock?

My query is ridiculously long so that's why I did not post it. If there are certain parts that you need let me know and I can post them.

Josh Pennington
  • 6,418
  • 13
  • 60
  • 93

2 Answers2

2

If you are in transaction isolation mode REPEATABLE_READ (which is the default), then selects should no create any locks. This is normal.

However if you're using insert ... select of course you'll get locks in the destination table.

So provided nobody else is writing to the destination table, AND there's at most one copy of your program running at once, you can never get a deadlock.

Deadlocks happen when two (or more) processes try to do conflicting things which could never complete. Normally this involves updating the same pair of rows in a differing order, but it can depend on your table structure.

ideas to consider:

  • Use an external lock (To innodb) to serialise the process against multiple copies
  • Change transaction isolation mode to READ_COMMITTED for this operation - if you understand what this means and can tolerate it.
  • Do less work in one transaction (commit more frequently)

You can see what the transactions involved in a deadlock were doing, using SHOW ENGINE INNODB STATUS, immediately after the deadlock.

You should be able to see what the other process(es) were and what they were doing. Consider turning the general log on or using other debug techniques.

Be sure to do any testing in a non-production system!

MarkR
  • 62,604
  • 14
  • 116
  • 151
2

You could try using INSERT DELAYED, here is what it does:

When a client uses INSERT DELAYED, it gets an okay from the server at once, and the row is queued to be inserted when the table is not in use by any other thread.

If you are willing to see data in a mixed state, you can change the transaction isolation level

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- Problematic SELECT query goes here --
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

I learned that one from another SO answer by Jon Erickson

Community
  • 1
  • 1
MBCook
  • 14,424
  • 7
  • 37
  • 41