Postgres ERROR: tuple concurrently updated
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.