Explicit table lock acquisition [CR 444921]

As of 12.7 ESD #3, the LOCK TABLE statement supports acquiring WRITE locks on a set of tables instead of a single table. See “LOCK TABLE support [CR 480880, CR 472791, CR 463196, 444921]” and “New and changed messages for LOCK TABLE [CR 444921, 472791]”.

The following information augments the section “How Locking Works,” in Chapter 10, “Transactions and Versioning.”

You can reserve WRITE locks on a set of tables within a new transaction using the LOCK TABLE statement. LOCK TABLE commits the current transaction and allows transactions to enqueue until the locks are available. For syntax, see “LOCK TABLE” in Chapter 6, “SQL Statements,” in the Sybase IQ Reference Manual.

For example, suppose that you are executing a critical transaction and you want to prevent update operation failure. Reserve WRITE locks on all the corresponding tables in advance, using commands like the following:.

CREATE PROCEDURE test_lock()
BEGIN
lbl:
LOOP
   LOCK TABLE t1, t2, t3 IN WRITE MODE WAIT “30:00:00”;
   IF SQLCODE <> 0 THEN
     PRINT 'Lock not yet acquired';
     ELSE
     LEAVE lbl;
   END IF;
END LOOP lbl;
UPDATE t1 set c1=5;
INSERT INTO t2 values(10,10);
TRUNCATE table t3;
COMMIT;
END;

In the case of deadlocks, the last LOCK TABLE statement that became blocked is rolled back and an error returns to that transaction about the form of deadlock that occurred.