Lock Resolution

Go Up to Specifying SET TRANSACTION Behavior

The lock resolution parameter determines what happens when a transaction encounters a lock conflict. There are two options:

• WAIT, the default, causes the transaction to wait until locked resources are released. Once the locks are released, the transaction retries its operation.
• NO WAIT returns a lock conflict error without waiting for locks to be released.

Because WAIT is the default lock resolution, you don’t need to specify it in a SET TRANSACTION statement. For example, the following statements are equivalent. They both start a transaction, t1, for READ WRITE access, WAIT lock resolution, and READ COMMITTED isolation level:

EXEC SQL
SET TRANSACTION NAME t1 READ WRITE READ COMMITTED;
EXEC SQL
SET TRANSACTION NAME t1 READ WRITE WAIT READ COMMITTED;

To use NO WAIT, the lock resolution parameter must be specified. For example, the following statement starts the named transaction, t1, for READ WRITE access, NO WAIT lock resolution, and SNAPSHOT isolation level:

EXEC SQL
SET TRANSACTION NAME t1 READ WRITE NO WAIT READ SNAPSHOT;

When lock resolution is specified, it follows the optional access mode, and precedes the optional isolation level parameter.

Tip:
It is good programming practice to specify a transaction’s lock resolution, even when it is WAIT. It makes an application’s source code easier to read and debug because the program’s intentions are clearly spelled out.

Advance To:

• RESERVING Clause