> ...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? -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
