Re: Small PosgreSQL locking function request - with bounty - Mailing list pgsql-general

> ...have you used the "for update" clause in your select statements?

Hi Ralf, thanks for the reply. I was unaware of the "for update"
construct. Thank you!

> My understanding is, that "for update" does what you need.

I've read through the documentation, found a few examples using "for
update" syntax, and it looks like you're right. Would transaction
level SERIALIZABLE also work? It does seem messier though, having to
handle errors thrown due to concurrent transactions.

I'm running to some trouble though with the SELECT FOR UPDATE
documentation found here --
http://www.postgresql.org/docs/9.0/static/sql-select.html#SQL-FOR-UPDATE-SHARE.
It says:

"...if an UPDATE, DELETE, or SELECT FOR UPDATE from another
transaction has already locked a selected row or rows, SELECT FOR
UPDATE will wait for the other transaction to complete, and will then
lock and return the updated row (or no row, if the row was deleted).
Within a SERIALIZABLE transaction, however, an error will be thrown if
a row to be locked has changed since the transaction started."

In my case I have a boolean "Active" column as a flag to indicate
whether the selected row is currently being crawled. This column is
used in querySelect to identify inactive rows. It is then modified by
queryUpdateActive within the same transaction to identify the row as
"currently being processed". Under the default READ COMMITTED
isolation level (without using the SELECT FOR UPDATE construct) I'm
running into the issue that concurrent transactions select the same
row to be crawled, and don't see the update from the other
transaction. My question is: according to the documentation, it seems
that SELECT FOR UPDATE may still run into the same concurrency issue.
If two concurrent transactions select the same row, the first will be
given the lock. The second transaction will encounter the lock and be
forced to wait. The update from the first transaction will occur, the
lock will be released, and control will be passed to the second
transaction. According to the documentation, the row will already have
been selected, so the transaction, it seems, will continue processing
the row as if it were marked "Inactive". In essence, the way I read
it, it won't care that the row had been updated by the first
transaction, and so essentially I will be running into the same
problem I'm facing now. Am I reading this correctly?