LOCK TABLE support [CR 480880, CR 472791, CR 463196, 444921]

Sybase IQ now allows users to reserve WRITE locks in advance to prevent failure of DML update statements like INSERT, DELETE, SYNCHRONIZE JOIN INDEX, UPDATE, and TRUNCATE due to version errors. Sybase IQ now also allows connections to enqueue for READ, WRITE, and EXCLUSIVE locks in case of lock unavailability.

As of 12.7 ESD #3, the LOCK TABLE statement supports acquiring WRITE locks on a set of tables as well as enqueuing on all the lock types. The following updates apply to existing documentation:

LOCK TABLE table-list [ WITH HOLD ] IN { SHARE |  WRITE | EXCLUSIVE } MODE [ WAIT [ time ] ] 

table-list:

[ owner. ] table-name [ , [ owner. ] table-name, … ]

time:

string 

For example, the following statement obtains a WRITE lock on the customer and employee tables, if available within 5 minutes and 3 seconds:

LOCK TABLE customer, employee IN WRITE MODE WAIT
'00:05:03'

The following statement waits indefinitely, until the WRITE lock on the customer and employee tables, if available or an interrupt occurs:

LOCK TABLE customer, employee IN WRITE MODE WAIT 

LOCK TABLE IN WRITE MODE unconditionally commits the connection's outermost transaction and returns successfully if all locks are available within the specified wait time. The snapshot of the database to be used by the transaction is established not by the LOCK TABLE IN WRITE MODE statement, but by the execution of the next command processed by Sybase IQ.

For all LOCK TABLE statement write mode failures, the user’s current transaction commits unless there is a syntax error.

A WRITE mode lock on an IQ table X that participates in a join index also locks:

• The top table of the join index hierarchy in WRITE mode when X is a non-top table

• The corresponding join virtual table (JVT)

LOCK TABLE grants write locks only if the user has permission to execute a DML update statement (INSERT/DELETE/UPDATE/TRUNCATE). The user must have one of these permissions on each table in the table-list. Even after write locks are granted, IQ checks for the appropriate permissions for subsequent commands. For example, INSERT permissions are checked when executing an INSERT statement.

Write locks can never be held across transactions (WITH HOLD mode is not supported). Once acquired, locks are released only when the transaction commits or rolls back, or when the connection disconnects. If necessary, the DBA can issue a DROP CONNECTION statement, causing the transaction to roll back.

LOCK TABLE on views is unsupported. Attempting to lock a view acquires a shared schema lock regardless of the mode specified in the command. A shared schema lock prevents other transactions from modifying the table schema.

The Transact-SQL (TSQL) stored procedure dialect does not support LOCK TABLE. For example, the following statement returns Syntax error near LOCK:

CREATE PROCEDURE tproc()
AS
BEGIN
COMMIT
LOCK TABLE t1 IN SHARE MODE
INSERT INTO t1 VALUES(30)
END

The Watcom-SQL stored procedure dialect supports LOCK TABLE. The default command delimiter is a semicolon (;). For example:

CREATE PROCEDURE wproc()
BEGIN
COMMIT;
LOCK TABLE t1 IN SHARE MODE;
INSERT INTO t1 VALUES (20);
END