CODEWITHSUNDEEP
sqloracle12c
Zampanò
Zampanò

Reputation: 594

Does 'For Update' Stop Other Connections from Inserting into Tables?

I understand that when the FOR UPDATE clause is issued in a query, as soon as the query is executed, the database automatically issues exclusive row-level locks on all rows returned by the SELECT statement which are held until a COMMIT or ROLLBACK command is issued.

These row-level locks prevent other connections from deleting or updating records in this query, but will they also prevent other users from inserting into the queried table while the locks are in place?

Upvotes: 1

Views: 105

Answers (1)

Littlefoot
Littlefoot

Reputation: 142778

No, SELECT ... FOR UPDATE won't prevent inserts.

Automatic Locks in DML Operations:

A row exclusive lock (RX), also called a subexclusive table lock (SX), indicates that the transaction holding the lock has updated table rows or issued SELECT ... FOR UPDATE. An SX lock allows other transactions to query, insert, update, delete, or lock rows concurrently in the same table. Therefore, SX locks allow multiple transactions to obtain simultaneous SX and SS locks for the same table.

Upvotes: 3

Related Questions