همانطور که می دانید، برای اجرای دستورات DMLای، رکوردها ابتدا قفل (Lock) می‌شوند و اگر Sessionای تلاش کند همان رکورد را تغییر دهد، باید تا زمان آزاد شدن قفل منتظر بماند و به صورت پیش فرض، این زمان انتظار نامحدود است. البته در نسخه‌های پیشین پایگاه داده اوراکل، عبارت WAIT همراه با دستور SELECT FOR UPDATE می توانست محدودیتی را در این زمینه ایجاد کند و به کاربران این امکان را می‌داد که مشخص کنند پیش از Rollback شدن دستور، چه مدت برای آزاد شدن قفل منتظر بمانند.

برای مثال:

Connected to Oracle Database 19c Enterprise Edition Release 19.0.0.0.0

SQL> select * from tbl for update wait 10;

ORA-30006: resource busy; acquire with WAIT timeout expired

به همین ترتیب، عبارت NOWAIT را می‌توان برای این منظور استفاده کرد که اگر رکورد موردنظر توسط تراکنش دیگری قفل شده باشد، دیتابیس بدون انتظار بلافاصله خطا برگرداند:

SQL> select * from tbl for update nowait;

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

اگر هیچ‌یک از عبارت‌های WAIT یا NOWAIT مشخص نشوند، اوراکل رفتار پیش‌فرض خود را حفظ کرده و تا زمان آزاد شدن قفل، به‌صورت نامحدود منتظر خواهد ماند.

SQL> select * from tbl for update;
executing

از نسخه 23.26.2 اوراکل 26ai، پشتیبانی از WAIT و NOWAIT به تمام دستورات DML شامل INSERT، UPDATE، DELETE و MERGE گسترش یافته است. این قابلیت به کاربران اجازه می‌دهد نحوه رفتار دیتابیس را زمانی که یک تراکنس قصد قفل کردن رکوردی را دارد که هم‌اکنون توسط تراکنش دیگری قفل شده است، کنترل کنند.

در صورت استفاده از NOWAIT، اگر قفل متناقضی وجود داشته باشد، دیتابیس بلافاصله دستور DML را Rollback کرده و خطا برمی‌گرداند. در مقابل، با استفاده از WAIT می‌توان مدت زمانی را مشخص کرد که دیتابیس پیش از Rollback شدن دستور برای آزاد شدن قفل منتظر بماند.

DELETE

رکوردی توسط session شماره یک پاک می شود(تراکنش commit نمی شود):

SQL> delete tbl where id=1;

1 row deleted

Session 2 تلاش می‌کند همان رکورد را حذف کند و پنج ثانیه منتظر می‌ماند، اما در نهایت با خطای Timeout مواجه می‌شود:

SQL> delete tbl where id=1 wait 5;

ORA-00054: Failed to acquire a lock (Type: "TX", Name: "Transaction", Description: "Lock held by a transaction to allow other transactions to wait for it") because it is currently held by another session. The resource being locked can be identified by 524315 ("usn<<16 | slot") and 536 ("sequence")

ORA-40097: Failure to acquire row lock, currently held by transaction ID 8.27.536

UPDATE

رکوردی توسط session شماره یک update می شود(تراکنش commit نمی شود):

SQL> update tbl set name='Vahid Yousefzadeh' where id=1 nowait;

1 row updated

Session 2 تلاش می‌کند همان رکورد را با استفاده از NOWAIT بروزرسانی کند و بلافاصله خطا دریافت می‌کند:

SQL> update tbl set name='Vahid Yousefzadeh' where id=1 nowait;

ORA-00054: Failed to acquire a lock (Type: "TX", Name: "Transaction", Description: "Lock held by a transaction to allow other transactions to wait for it") because it is currently held by another session. The resource being locked can be identified by 65540 ("usn<<16 | slot") and 531 ("sequence")

ORA-40097: Failure to acquire row lock, currently held by transaction ID 1.4.531

INSERT

در ابتدا session 1 رکوردی را در جدول درج می کند(تراکنش commit نمی شود):

SQL> insert into tbl values(1,'Vahid YousefZadeh') wait 2;

1 row inserted

Session 2 تلاش می‌کند همان رکورد را درج کند و دو ثانیه منتظر می‌ماند، اما پس از پایان زمان انتظار، دستور Rollback شده و خطا برگردانده می‌شود:

SQL> insert into tbl values(1,'Vahid YousefZadeh') wait 2;

ORA-00054: Failed to acquire a lock (Type: "TX", Name: "Transaction", Description: "Lock held by a transaction to allow other transactions to wait for it") because it is currently held by another session. The resource being locked can be identified by 655369 ("usn<<16 | slot") and 528 ("sequence")

ORA-40097: Failure to acquire row lock, currently held by transaction ID 10.9.528

MERGE

رکوردی توسط session شماره یک update می شود:

SQL> update tbl set name='Vahid Yousefzadeh' where id=1;

1 row updated

Session 2 دستور MERGE را همراه با عبارت WAIT اجرا می‌کند:

MERGE INTO tbl t
USING (SELECT 1 id, 'Usef' name FROM dual) s
ON (t.id = s.id)
WHEN MATCHED THEN
  UPDATE SET t.name = s.name
WHEN NOT MATCHED THEN
  INSERT (id, name) VALUES (s.id, s.name) wait 2;

ORA-00054: Failed to acquire a lock (Type: "TX", Name: "Transaction", Description: "Lock held by a transaction to allow other transactions to wait for it") because it is currently held by another session. The resource being locked can be identified by 196640 ("usn<<16 | slot") and 573 ("sequence")

ORA-40097: Failure to acquire row lock, currently held by transaction ID 3.32.573