Example: LOCKING ROW - Analytics Database - Teradata Vantage

This example shows the proper use of a rowhash lock.

     CREATE TABLE customer (
       cust_id INTEGER,
       phone   INTEGER,
       fax     INTEGER,
       telex   INTEGER)
     PRIMARY INDEX (cust_id),
     UNIQUE INDEX(fax),
     INDEX(telex);

     CREATE TABLE sales (
       custcode INTEGER,
       zip      INTEGER,
       salesvol INTEGER);

User A:

     BEGIN TRANSACTION;

     LOCKING ROW EXCLUSIVE
     SELECT phone
     FROM customer
     WHERE cust_id=12345;

     UPDATE customer
     SET phone=3108292488
     WHERE cust_id=12345;

The User A EXCLUSIVE rowhash lock prevents another user from accessing the same row.

In the following, the user A rowhash WRITE lock, in conjunction with LOCKING TABLE, prevents user B from accessing the same row:

User A:

     BEGIN TRANSACTION;

     LOCKING TABLE sales FOR READ,
     LOCKING ROW FOR WRITE
     SELECT telex
     FROM customer
     WHERE fax=0;

     UPDATE customer
     SET telex=0
     WHERE fax=0;

     SELECT zip
     FROM sales
     WHERE custcode=111;

     SELECT salesvol
     FROM sales
     WHERE custcode=222;

     ...

     END TRANSACTION;

User B:

     BEGIN TRANSACTION;

     LOCKING ROW FOR WRITE
     SELECT *
     FROM customer
     WHERE cust_id=12345
     INSERT INTO customer (12345, 3108284422, 3108684231, 5555);

     END TRANSACTION;

The User B LOCKING ROW FOR WRITE modifier waits until the User A transaction ends before it can be completed.