Geek Answers Handbook

How to mark some nr rows in a table while accessing

Our application has a table called cargo_items. It can be considered as a kind of queue for the subsequent processing of these elements. Initially, there was one job that took 3,000 records and processed them one by one. Later, someone decided to launch 3 more copies of the same work. What happened is quite obvious, many items have been processed twice.

My task is to work correctly with these processes if many instances are running at the same time. The solution I'm going to now is to mark 3000 entries in the database with job_id, and then get all these objects and process them isolated from other processes.

My current approach for marking these lines is as follows:

UPDATE cargo_item item
SET job_id = 'SOME_UUID', job_ts = now()
FROM  ( 
   SELECT id
   FROM   cargo_item
   WHERE  state='NEW' AND job_id is null 
   LIMIT  3000
   FOR UPDATE
   ) sub
WHERE  item.id = sub.id;

Basically this approach blocks 3,000 rows for updating. I am not sure if this is a good approach.

In another thread, I read about using advisory locks for this scenario.

What do you guys think about the current approach and use advisory locking instead?

UPDATE

As suggested, I would adapt the update statement as follows:

UPDATE cargo_item item
SET job_id = 'SOME_UUID', job_ts = now()
FROM  ( 
   SELECT id
   FROM   cargo_item
   WHERE  state='NEW' AND job_id is null 
   ORDER  BY id
   LIMIT  3000
   FOR UPDATE
   ) sub
WHERE  item.id = sub.id;

Thanks to Erwin and Tometzky for the tip. However, I wonder if the way I am trying to solve the problem is good? Are there any different approaches you could think of?

+1
source share
3 answers

In the corresponding answer, you mean:

, . , - .

, 3000 . , , . :

, , , , , .

ORDER BY .

UPDATE cargo_item item
SET job_id = 'SOME_UUID', job_ts = now()
FROM  ( 
   SELECT id
   FROM   cargo_item
   WHERE  state='NEW' AND job_id is null 
   ORDER  BY id
   LIMIT  3000
   FOR UPDATE
   ) sub
WHERE  item.id = sub.id;

, , . ( "" .) , , id.

. () .

, (, ). , , .

UPDATE

, . Concurrency - , .

UPDATE. n (3000 ) UUID . .

UPDATE cargo_item c
SET    job_id = u.uuid_col
     , job_ts = now()
FROM  (
   SELECT row_number() OVER () AS rn, uuid_col
   FROM   uuid_tbl WHERE  <some_criteria>  -- or see below
   ) u
JOIN (
   SELECT (row_number() OVER () / 3000) + 1 AS rn, item.id 
   FROM   cargo_item
   WHERE  state = 'NEW' AND job_id IS NULL
   FOR    UPDATE   -- just to be sure
   ) c2 USING (rn)
WHERE  c2.item_id = c.item_id;

  • . 1 3000 , 2 3000 . .

  • , ORDER BY row_number() .

  • UUID (uuid_tbl), VALUES . .

  • 3000 . 3000, 3000 .

+1

. , order by id .

, id , . , , .

. , .

+1

.

   SELECT id
   FROM   cargo_item
   WHERE  pg_try_advisory_lock(id)
   LIMIT  3000
   FOR UPDATE

, , pg_try_advisory_lock (id). , pg_advisory_unlock

0

More articles:


All Articles