transactions in postgresql and hibernate

Question

I call this Postgres function from a few threads through Hiberante:

UPDATE table SET active = _active 
    WHERE id IN (
            SELECT id FROM table 
            WHERE active = 0 ORDER BY id LIMIT 5);

    SELECT count('PK') INTO cnt FROM table WHERE active = _active;

    IF(cnt > 0)THEN
            FOR ret IN 
                    SELECT * FROM table WHERE active = _active
                    LOOP
                    RETURN NEXT ret;
            END LOOP;
END IF;

I get data for few threads, handle it and after that remove from table. Is it safe? Should I use 'Select for update' or something else?

Stephen Frost · Accepted Answer · 2016-06-08 02:10:38Z

The initial UPDATE statement will lock the rows. Turning the SELECT into a SELECT FOR UPDATE would be good if it's possible there are other records (besides those that the UPDATE impacted) which you wish to lock for update.

Note that locks in PostgreSQL are, once acquired, held until transaction completion; there's no need to 're-lock' any records. Further, you should always try to avoid 'lock escalation', which is where you take a lower-weight lock and then attempt to acquire a higher-weight lock later. That tends to result in deadlock scenarios.

What you might wish to investigate for this particular type of query is the PostgreSQL "SKIP LOCKED" feature, which you can read about here:

http://michael.otacoo.com/postgresql-2/postgres-9-5-feature-highlight-skip-locked-row-level/

This feature allows you to have multiple worker processes querying from a single table, each grabbing whatever the next unlocked row is (and then locking it, in one step); it's a great approach to building job queues in PostgreSQL.

Collectives™ on Stack Overflow

transactions in postgresql and hibernate

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related