next up previous contents index
Next: Deadlocks Up: Transactions and Locks Previous: Read Committed and Serializable

  
Locking

Exclusive locks, also called write locks, prevent other users from modifying a row or an entire table. Rows modified by UPDATE  and DELETE  are then exclusively locked automatically for the duration of the transaction. This approach prevents other users from changing the row until the transaction is either committed or rolled back.

Table [*] shows two simultaneous UPDATE transactions affecting the same row.

 
Table: Waiting for a lock
Transaction 1 Transaction 2 Description
BEGIN WORK BEGIN WORK start both transactions
UPDATE row 64   transaction 1 exclusively locks row 64
  UPDATE row 64 transaction 2 must wait to see if transaction 1 commits
COMMIT WORK   transaction 1 commits; transaction 2 returns from UPDATE
  COMMIT WORK transaction 2 commits


The first transaction must wait to see whether the second transaction commits or rolls back.   If SERIALIZABLE isolation level had been used, transaction 2 would have been rolled back automatically if transaction 1 committed.

The only time when users must wait for other users is when they are trying to modify the same row. If they modify different rows, no waiting is necessary. SELECT queries never have to wait.

The database performs locking automatically. In certain cases, however, locking must be controlled manually. As an example, Figure [*] shows a query that first SELECTs a row, then performs an UPDATE.  

        test=> BEGIN WORK;
        BEGIN
        test=> SELECT *
        test-> FROM lock_test
        test-> WHERE name = 'James';
         id  |              name              
        -----+--------------------------------
         521 | James                         
        (1 row) 
         
        test=> --
        test=> -- the SELECTed row is not locked
        test=> --
        test=> UPDATE lock_test
        test-> SET name = 'Jim'
        test-> WHERE name = 'James';
        UPDATE 1
        test=> COMMIT WORK;
        COMMIT
 

The problem arises because another user can modify the James row between the SELECT and UPDATE. To prevent this problem, you can use SERIALIZABLE  isolation. In this mode, however, one of the UPDATEs would fail.

  A better solution is to use SELECT...FOR UPDATE to lock the selected rows. Figure [*] shows the same query using SELECT...FOR UPDATE.

 

        test=> BEGIN WORK;
        BEGIN
        test=> SELECT *
        test-> FROM lock_test
        test-> WHERE name = 'James'
        test-> FOR UPDATE;
         id  |              name              
        -----+--------------------------------
         521 | James                         
        (1 row) 
         
        test=> --
        test=> -- the SELECTed row is locked
        test=> --
        test=> UPDATE lock_test
        test-> SET name = 'Jim'
        test-> WHERE name = 'James';
        UPDATE 1
        test=> COMMIT WORK;
        COMMIT
 

Another user cannot modify the James row between the SELECT...FOR UPDATE and UPDATE. In fact, the row remains locked until the transaction ends.  

You can also manually control locking by using the LOCK command. It allows specification of a transaction's lock type and scope. See the LOCK manual page for more information.


next up previous contents index
Next: Deadlocks Up: Transactions and Locks Previous: Read Committed and Serializable
Bruce Momjian
2001-05-09