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
MySQL get a lock to update certain rows and prevent other sessions to read that row
lockingMySQL
Related Question
- Sql-server – How to get SQL insert and/or update to not lock entire table on MS SQL Server
- Mysql – Select query on an index column with gap locking
- Sql-server – TSQL and Table Lock on one row
- MySQL: will a transaction lock the row
- Oracle Database troubleshooting enq: TX – row lock contention
- Sql-server – Read Committed Shared Lock
Best Answer
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
orSELECT .. LOCK IN SHARE MODE
, or by using the "serializable"-isolation level for the second transaction, which implicitly adds theLOCK IN SHARE MODE
for you to every select in that transaction: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
).