I have a table with time-related data. Think about weather data – some data inserted at a time may be the same as data inserted at a much earlier time.
The only originality in my case is that I do not want data to be inserted if the most recent data, provided by the same person, is identical.
My table is the following
- personid, which is a FOREIGN KEY on another table
- time,
- col1,
- col2
I have the following query that tries to do what I want:
INSERT INTO table
SELECT :personid, :col1, :col2
WHERE NOT EXISTS (SELECT * FROM table WHERE person=:personid, col1=:col1, col2=:col2 ORDER BY time DESC LIMIT 1)
This query is meant to INSERT a row if and only if the most recent row for :personid in the table, if any, does not already have values :col1, :col2. This query is expected to be fast because, although the table is very large, :personid-specific data does not ever exceed a few hundred rows.
Most times, this query succeeds. Sometimes, however, it raises the following error:
SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction
(My query is executed as part of a REPEATABLE READ transaction, which I have currently no control over.)
What causes this behavior and how to avoid it? I wonder if this isn’t linked to the fact I am using personid, which is a foreign key.