I'm involved in a project using a postgresql database for it's backend.
Currently we see a lot of deadlocks. I'll try to illustrate our problem:
We have a table, table1, in which a lot of transactions takes place,
both select, update, insert and delete transactions.
The table can be illustrated like this:
+----+------+-------+-------+-------+
| ID | USER | DATA1 | DATA2 | DATA3 |
+----+------+-------+-------+-------+
We have B-tree indexes on ID and USER.
Whenever a row is unused, USER is NULL. These rows are NOT touched by
the transactions mentioned above.
We have a separate script running to assign these rows to users, using a
statement like this:
SELECT table1.Id, table1.User, table1.Data1, table2.data1
FROM table1, table2
FOR UPDATE OF table1
LIMIT 1000
The script then runs through these rows and assigns the different rows
to users, using a simple "UPDATE table1 SET USER=?" statement. And here
the fun begins. My idea is that the SELECT statement would give me
exclusive locks to these rows (while letting the rest of the rows in the
table being unlocked), but this script keeps running into deadlocks,
even though I should keep this lock until I commit, which doesn't happen
until the script is done.
Does anybody have an explanation for this phenomenon?
--
Andreas Plesner Jacobsen | Adults die young.