Does "Select for Update" Prevent Other Connections Inserting When the Row Is Not Present

Does SELECT FOR UPDATE prevent other connections inserting when the row is not present?

In Oracle, the SELECT ... FOR UPDATE has no effect on a non-existent row (the statement simply raises a No Data Found exception). The INSERT statement will prevent a duplicates of unique/primary key values. Any other transactions attempting to insert the same key values will block until the first transaction commits (at which time the blocked transaction will get a duplicate key error) or rolls back (at which time the blocked transaction continues).

Can Select.... for Update lock the row from other Selects?

No. In Oracle, readers do not block writers and writers do not block readers. Updating a row (or locking it in preparation for updating it) cannot block a pure reader. Actually updating the row may slow down queries which may have to apply UNDO in order to get back the version of the block that they need to read but it won't block the reader.

Have select for update block on nonrexisting rows

I am not sure how select for update handles non-existing rows.

It doesn't.

The best you can do is to use an advisory lock if you know something unique about the new row. (Use hashtext() if needed, and the table's oid to lock it.)

The next best thing is a table lock.

That being said, your question makes it sound like you're locking way more than you should. Only lock rows when you actually need to, i.e. write operations.

Mysql select for update - it is not locking the target rows. How do I make sure it does?

A SELECT FOR UPDATE locks the row you selected for update until the transaction you created ends. Other transactions can only read that row but they cannot update it as long as the select for update transaction is still open.

In order to lock the row(s):

START TRANSACTION;
SELECT * FROM test WHERE id = 4 FOR UPDATE;
# Run whatever logic you want to do
COMMIT;

The transaction above will be alive and will lock the row until it is committed.

In order to test it, there are different ways. I tested it using two terminal instances with the MySQL client opened in each one.

On the first terminal you run the SQL:

START TRANSACTION;
SELECT * FROM test WHERE id = 4 FOR UPDATE;
# Do not COMMIT to keep the transaction alive

On the second terminal you can try to update the row:

UPDATE test SET parent = 100 WHERE id = 4;

Since you create a select for update on the first terminal the query above will wait until the select for update transaction is committed or it will timeout.

Go back to the first terminal and commit the transaction:

COMMIT;

Check the second terminal and you will see that the update query was executed (if it did not timed out).

Why use SELECT FOR UPDATE? (MySQL)

SELECT ... FOR UPDATE will lock the record with a write (exclusive) lock until the transaction is completed (committed or rolled back).

To select a record and ensure that it's not modified until you update it, you can start a transaction, select the record using SELECT ... FOR UPDATE, do some quick processing, update the record, then commit (or roll back) the transaction.

If you use SELECT ... FOR UPDATE outside of a transaction (autocommit ON), then the lock will still be immediately released, so be sure to use a transaction to retain the lock.

For performance, do not keep transactions open for very long, so the update should be done immediately.

How do I lock on an InnoDB row that doesn't exist yet?

If there is an index on username (which should be the case, if not, add one, and preferably a UNIQUE one), then issuing a SELECT * FROM user_table WHERE username = 'foo' FOR UPDATE; will prevent any concurrent transaction from creating this user (as well as the "previous" and the "next" possible value in case of a non-unique index).

If no suitable index is found (to meet the WHERE condition), then an efficient record-locking is impossible and the whole table becomes locked*.

This lock will be held until the end of the transaction that issued the SELECT ... FOR UPDATE.

Some very interesting information on this topic can be found in these manual pages.

* I say efficient, because in fact a record lock is actually a lock on index records. When no suitable index is found, only the default clustered index can be used, and it will be locked in full.

Multiple SELECT ... FOR UPDATE with a delayed INSERT INTO

I think what is happening is, that you start 2 transactions. Both get a "select ... for update" on the same table. You would expect the second transaction to wait before the update. But from the docs https://dev.mysql.com/doc/refman/5.7/en/innodb-locking-reads.html it sounds like it will not (and this is the behaviour you see). So both "selects ... for update" block each other, leaving you with a deadlock.

When to use SELECT ... FOR UPDATE?

The only portable way to achieve consistency between rooms and tags and making sure rooms are never returned after they had been deleted is locking them with SELECT FOR UPDATE.

However in some systems locking is a side effect of concurrency control, and you achieve the same results without specifying FOR UPDATE explicitly.

---

To solve this problem, Thread 1 should SELECT id FROM rooms FOR UPDATE, thereby preventing Thread 2 from deleting from rooms until Thread 1 is done. Is that correct?

This depends on the concurrency control your database system is using.

• MyISAM in MySQL (and several other old systems) does lock the whole table for the duration of a query.

• In SQL Server, SELECT queries place shared locks on the records / pages / tables they have examined, while DML queries place update locks (which later get promoted to exclusive or demoted to shared locks). Exclusive locks are incompatible with shared locks, so either SELECT or DELETE query will lock until another session commits.

• In databases which use MVCC (like Oracle, PostgreSQL, MySQL with InnoDB), a DML query creates a copy of the record (in one or another way) and generally readers do not block writers and vice versa. For these databases, a SELECT FOR UPDATE would come handy: it would lock either SELECT or the DELETE query until another session commits, just as SQL Server does.

When should one use REPEATABLE_READ transaction isolation versus READ_COMMITTED with SELECT ... FOR UPDATE?

Generally, REPEATABLE READ does not forbid phantom rows (rows that appeared or disappeared in another transaction, rather than being modified)

• In Oracle and earlier PostgreSQL versions, REPEATABLE READ is actually a synonym for SERIALIZABLE. Basically, this means that the transaction does not see changes made after it has started. So in this setup, the last Thread 1 query will return the room as if it has never been deleted (which may or may not be what you wanted). If you don't want to show the rooms after they have been deleted, you should lock the rows with SELECT FOR UPDATE

• In InnoDB, REPEATABLE READ and SERIALIZABLE are different things: readers in SERIALIZABLE mode set next-key locks on the records they evaluate, effectively preventing the concurrent DML on them. So you don't need a SELECT FOR UPDATE in serializable mode, but do need them in REPEATABLE READ or READ COMMITED.

Note that the standard on isolation modes does prescribe that you don't see certain quirks in your queries but does not define how (with locking or with MVCC or otherwise).

When I say "you don't need SELECT FOR UPDATE" I really should have added "because of side effects of certain database engine implementation".

SELECT FOR UPDATE holding entire table in MySQL rather than row by row

The default isolation level for InnoDB tables is repeatable read. When this isolation level is active we get the following behavior (quote from: https://dev.mysql.com/doc/refman/5.5/en/set-transaction.html):

For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE),
UPDATE, and DELETE statements, locking depends on whether the
statement uses a unique index with a unique search condition, or a
range-type search condition. For a unique index with a unique search
condition, InnoDB locks only the index record found, not the gap
before it. For other search conditions, InnoDB locks the index range
scanned, using gap locks or next-key (gap plus index-record) locks to
block insertions by other sessions into the gaps covered by the range.

In other words: could you try using the primary key in the WHERE condition of the SELECT? So for instance instead of:

START TRANSACTION;
SELECT * FROM productMacAddress WHERE status='free' limit 8 FOR UPDATE;

Try:

START TRANSACTION;
SELECT * FROM productMacAddress WHERE id=10 FOR UPDATE;

in case id is the primary key. Any other column with a unique index on it would work too. When using non-unique columns in your WHERE clause InnoDB will lock a range of rows.

InnoDB Select For Update

Yes, collisions can always occur. So using the design pattern:

SELECT FOR UPDATE for all the resources in a given transaction first, can prevent or shorten deadlock situations.

Suppose the following scenario:

• Process A updates table 1 & 2 in the order 1,2
• Process B updates table 1 & 2 in the order 2,1

Now process A updates, table 1, with process B updating table 2 at the same time, causing a deadlock (assumption is that the same "records/pages" are hit by this update).

If however the SELECT FOR UPDATE would be used in the start of the transaction, the transaction would block at the start since table 2 (or 1 whomever is faster) can not be locked (yet). The key part here is "At the start of the transaction", if you do it later, then just running the UPDATE is just as efficient.

Key is always to keep your transactions atomic and fast: Group the SQL logic so it can execute with the least amount of other code in between, keep lock times as short as possible.

---

Related Topics