Postgres ERROR: tuple concurrently updated

Question

I have a large table test in which in user_id 2 have 500000 records. So I want to delete this record in chunks of 100 records but it is given error. Here is my query:

delete from test where test_id in (select test_id
from test where User_id = 2 limit 100 )

ERROR: tuple concurrently updated

What is the issue. How can i solved it.

Answer 1

Your plain subselect fetches up to 100 rows, but does not lock them against write access. Concurrent transactions can update or delete one or more of those rows before DELETE can lock the rows (at least with the default isolation level READ COMMITTED). This would result in your error message.

To defend against this race condition, lock the rows in the SELECT with FOR UPDATE (or other options):

DELETE FROM test t
USING (
   SELECT test_id
   FROM   test
   WHERE  User_id = 2
   ORDER  BY test_id  -- acquire locks in consistent order!
   LIMIT  100
   FOR UPDATE         -- lock rows
   ) x
WHERE x.test_id = t.test_id;

Do this in a consistent order to prevent deadlocks between multiple transactions collecting locks in different order and ending up blocking each other out.

There are even better solutions. The best option would be with FOR UPDATE SKIP LOCKED in Postgres 9.5 or later:

• Postgres UPDATE ... LIMIT 1

Consider upgrading to a current version of Postgres.

Answer 2

Finally I got solution, I am just doing reindex the table, and its work properly now.

reindex table test

Thank you, all of you for your suggestion.

Answer 3

You may want to try:

1. Separate getting the candidate rows for deletion, from Deleting them
   For example:
   CREATE TEMP TABLE tmp_test1 AS SELECT test_id FROM test WHERE user_id = 2;
   CREATE TEMP TABLE tmp_test2 AS SELECT * FROM tmp_test1 LIMIT 100;
   {Loop while tmp_test2 has rows}
   DELETE FROM test WHERE test_id IN(SELECT test_id FROM tmp_test2);
   DELETE FROM tmp_test1 WHERE test_id IN(SELECT test_id FROM tmp_test2);
   TRUNCATE TABLE tmp_test2;
   INSERT INTO tmp_test2 SELECT * FROM tmp_test1 LIMIT 100;
   {End Loop}

2. Deleting 0.5M rows?
   What percentage of the table does user 2's data occupy?
   If it's more than a fraction, you may consider re-creating the table without this data.