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.