Oracle Enqueue Mechanism
Published by : Obay Salah , November 19, 2024
Let's assume a session requests a lock on a table or a specific field but finds that field or table locked by another session. This session will remain in a waiting state.
Now, what if multiple sessions make the same request? These requests will be queued until the lock is released, allowing the next session to acquire the lock. This is called "enqueueing."
However, how can we bypass the waiting process if there is a lock on the table or field?
The solution in such cases is the NOWAIT option. Of course, a SELECT operation does not require locking, but DML operations do.
We can use the NOWAIT option to bypass the waiting process during DML operations if we find that the table or row is locked by another session.
As mentioned before, a SELECT operation does not need to lock fields or tables. However, the SELECT ... FOR UPDATE statement will lock the targeted fields. If those fields are found to be locked by another session, there are two options: the first is WAIT, which means waiting for a specified duration (in seconds), and the second is NOWAIT, which allows the operation to fail immediately if the fields are locked by another session.
SELECT * FROM TEST FOR UPDATE NOWAIT;
Comments
no comment yet!