Transactions isolation level and locking records, disable SELECT on records that are in running transaction

Admin 1
Viewed 423

Can I disable SELECT on rows that are been used inside of a transaction?

For example, rows in table potatoes are being destroyed in transaction 1, and it takes a long time to do so. In transaction 2, there is SELECT* on the potatoes table. I don't want to select those records that are being used in transaction 1.

So, the result for SELECT* potatoes is without the records that are being used in transaction 1.

Is isolation level serializable right fit for transaction 1, so transaction 2 can't select those rows? Or I need to lock those records with some lock mode?

I'm trying to avoid flags in the database or Redis.

Things I found:

postgresql ruby-on-rails
1 Answers

This is a strange requirement. Logically, the potatoes are not deleted until your transaction completes – after all, you can still roll back the transaction, and then it has never happened. What is important is the logical order of transactions, and normally there is nothing wrong with assuming that the transaction that selected the potatoes happened logically before the deleting transaction.

All that said, if you still insist on your requirement, here's how you can do it:

The deleting transaction simply deletes potatoes using DELETE, but you use a modified query to select potatoes:

SELECT /* whatever */
FROM potatoes
WHERE /* condition */
FOR SHARE OF potatoes SKIP LOCKED;

Different from a regular SELECT, this query will place a SHARE lock on each row selected. Such locks don't conflict with each other, so several such queries can select the same potatoes.

However, the lock will conflict with the EXCLUSIVE row lock of the DELETE, so it cannot select such rows. To avoid blocking when such a row is encountered, you add SKIP LOCKED as in my example.

All this is not for free: a row lock modifies the table row, so such queries will cause writes as well as reads.

user_64643080
Related