4

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.

  • Are you on a current point release of 9.0? – Craig Ringer Jan 13 '17 at 6:04
  • Are you running these batches in parallel or serial? Could you paste the code that you're using? Also, version 9.0 is old. I mean Sept 2010 old. – Evan Carroll Jan 13 '17 at 6:18
  • i am using 9.0 old versions – Saddam Khan Jan 13 '17 at 6:26
  • 1
    But which point release exactly? The last one was 9.0.23 before 9.0 was retired in Sept 2015. Also: do you have concurrent write access? If so, what's possible? – Erwin Brandstetter Jan 14 '17 at 19:09
5

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:

Consider upgrading to a current version of Postgres.

2

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.

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.