3

my question is: How can I get a certain row to update and on the other side, block that row to be selected by other sessions. I don't want a simple SELECT .. FOR UPDATE or SELECT .. LOCK IN SHARE MODE. I want the second session not to be able to select the row at all. Maybe some isolation level, I tried them all but nothing. Thanks

  • Please explain why you want that behavior. Then we will explain a different way to achieve that goal. – Rick James Aug 18 '17 at 3:09
  • If the particular row is being updated I don't want other sessions to be able to select it and look at its data. Like skipping it at all – anisakras Aug 21 '17 at 6:21
2

Unless you set the transaction level to read uncommitted, a transaction will see the unmodified row. The second session only has to acknowledge the existence of the first session when both sessions want to lock that row, otherwise they will act as if the other transaction doesn't exist (as that session could rollback at any time) - which is the whole point of using transactions.

In MySQL, you can get a locking read by either explicitly using SELECT .. FOR UPDATE or SELECT .. LOCK IN SHARE MODE, or by using the "serializable"-isolation level for the second transaction, which implicitly adds the LOCK IN SHARE MODE for you to every select in that transaction:

This level is like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT ... LOCK IN SHARE MODE if autocommit is disabled. If autocommit is enabled, the SELECT is its own transaction. It therefore is known to be read only and can be serialized if performed as a consistent (nonlocking) read and need not block for other transactions. (To force a plain SELECT to block if other transactions have modified the selected rows, disable autocommit.)

So if you can force the second transaction to use that transaction level and to not enable autocommit, you can lock that row for this session; but keep in mind that this mode can slow down concurrent queries, as every read locks, so you might want to use this mode only when you need it.

This behaviour complies with the requirements of the sql standard. The exact implementation is a design choice, and other databases will do this differently. E.g. the MSSQL Server will not read your modified, uncommitted row (unless the isolation level is read uncommitted).

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.