Introduction
NOWAIT option in Oracle allows acquiring lock immediately without waiting until the it is available or time-out period expires (60 sec in Oracle). If it is not possible the exception is thrown in no time, without any delay.
Look at this example.
Create a simple table
create table testl (x int);
From one session execute:
lock table testl in exclusive mode
And from the second:
select * from testl for update nowait
The second will fail immediately giving the exception.
ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
DB2
DB2 does not support NOWAIT option. Default behavior is to wait indefinitely until resource is available. But it is possible to emulate NOWAIT behavior in three different ways.
DB2 Global for the whole database
One can set global database parameter
LOCKTIMEOUT to 0 by issuing command:
DB2 UPDATE DB CONFIGURATION FOR
USING LOCKTIMEOUT 0
The disadvantage of this approach is that this behaviour will be default for all statements executed against this database.
DB2 Connection level
Another possibility is to set connection parameter LockTimeout as 0. For ODBC/CLI connection it can be set in
db2cli.ini file. For JDBC connection
dbcurrentLockTimeout connection attribute should be used. But the disadvantage is as above - it is default for all statements executed via this connection which is not always desirable.
DB2 Set temporarily
In DB2 it also possible to set locktime out parameter after connection is established by executing the
command:
db2 SET CURRENT LOCK TIMEOUT 0
So we can emulate NOWAIT clause by running the following steps
- Remember the current value of LOCK TIMEOUT parameter.
- Set this parameter to 0.
- Execute the statement.
- Restore previous value.
This way we can set locktimeout value only for one statement just allowing the other statements be executed as usually.
Delphi program
To verify how it works from the application I created a simple Delphi console program (using
XE3 demo version).
The program consists of two parts.
DBAccess encapsulates some database access method and
UnitNoWait is the test itself.
The NOWAIT emulation is defined as:
procedure DBConnect.executeSQLNoWait(Q: TSQLQuery; withOpen : boolean);
var
timeout: integer;
QQ: TSQLQuery;
inn : boolean;
begin
case T of
DB2:
begin
QQ := getQ('SELECT CURRENT LOCK TIMEOUT AS C FROM DUAL');
with QQ do
begin
ExecSql;
Open;
timeout := FieldByName('C').AsInteger;
Close();
end;
QQ := getQ('SET CURRENT LOCK TIMEOUT 0');
QQ.ExecSQL;
QQ.Close;
{ In case of exception TIMEOUT will not be restored.
Find better implementation for real environment. }
{ Run statement now. }
Q.ExecSQL;
if (withOpen) then Q.Open;
inn := Conn.InTransaction;
{ restore original value }
QQ := getQ('SET CURRENT LOCK TIMEOUT ' + IntToStr(timeout));
QQ.ExecSql;
QQ.Close;
end;
Oracle:
with Q do
begin
{ IMPORTANT: run executeSQLNoWait only once for the statement. Find better
solution for real environment. }
SQL.Add(' NOWAIT');
ExecSql();
end;
end;
end;
And the test:
Constructor TestNoWait.Create(paramT: DatabaseType);
begin
DBTest := DBConnect.Create(paramT);
end;
procedure TestNoWait.runQueryNoWait;
var
Q: TSQLQuery;
begin
Q := DBTest.getQ('SELECT * FROM TESTL FOR UPDATE');
DBTest.executeSQLNoWait(Q,true);
Q.Close;
Q := DBTest.getQ('SELECT * FROM TESTL FOR UPDATE');
Q.ExecSQL();
Q.Open;
Q.Close;
end;
It works for Oracle.
For DB2 we have to perform two tests to be sure that is works as expected.
First test. Execute the following statement from the console (+c parameter overcomes autocommit which is default for CLP).
db2 +c lock table testl in exclusive mode
Then launch Dephi test, it should fail at the first statement in no time throwing SQL0911N sql error code.
Second test should verify that default value is restored after executing the first statement. It can be accomplished by using debugger. Simpy just stop execution immediately after DBTest.executeSQLNoWait(Q,true) statement. At this moment execute again
db2 +c lock table testl in exclusive mode
and continue with Delphi. The second statement should hang. Wait for some time and execute:
db2 commit
The execution of the Delphi program should be resumed.
Oracle WAIT n
Oracle allows also to specify number of seconds to wait. For instance (set lock timeout to 5 seconds).
select * from testl for update wait 5
But this clause can be also easily implemented in DB2 using method described above. Just add additional parameter to executeSQLNoWait procedure and replace 0 with this value in 'SET CURRENT LOCK TIMEOUT 0' statement
