Home What is the benefit of embedding pg_advisory_lock inside SELECT vs explicitly using another statement?
Reply: 1

What is the benefit of embedding pg_advisory_lock inside SELECT vs explicitly using another statement?

Zanko
1#
Zanko Published in 2017-09-13 21:08:30Z

I cannot really understand the example from https://www.postgresql.org/docs/9.1/static/explicit-locking.html

where they embed the lock along with the SELECT clause SELECT pg_advisory_lock(id) FROM foo WHERE id = 12345; -- ok

What is it selecting from FOO? I would understand better if it is like

SELECT pg_advisory_lock(123); //lock
SELECT * FROM foo WHERE id = 12345;

where it is explicitly locking the block. I can't seem to find explanation on how to really use advisory lock anywhere that explains the differnece between both Embedding and Explicitly on its own statement.

JGH
2#
JGH Reply to 2017-09-14 12:36:17Z

Doing SELECT pg_advisory_lock(123); will create a lock on 123, whether it is a valid value or not. Doing SELECT pg_advisory_lock(id) FROM foo WHERE id = 123; will create the lock only if there is an entry for ID 123 in table foo.

Let's note the line found in the pg_locks doc:

The actual meaning of the keys is up to the user

which tends to imply that the select/from/where syntax is for associating the lock to an existing row, while the lone select syntax is for a broader meaning, like an application-wide lock.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.304177 second(s) , Gzip On .

© 2016 Powered by cudou.com design MATCHINFO