Postgres ERROR: tuple concurrently updated

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.

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.

StackExchange Database Administrators Q#160954, answer score: 7