Postgresql - SELECT FOR UPDATE example

The Postgresql documentation says:

FOR UPDATE causes the rows retrieved by the SELECT statement to be locked as though for update. This prevents them from being modified or deleted by other transactions until the current transaction ends. That is, other transactions that attempt UPDATE, DELETE, or SELECT FOR UPDATE of these rows will be blocked until the current transaction ends.

Let's see a little and easy example just to verify if this is true or not :-)

First consider this table (very very simple):

   1 CREATE TABLE "public"."example" (
   2   "id" INTEGER
   3 ) WITHOUT OIDS;

Now insert just one row inside this table, then use this script to create a pl/pgsql function that will act as a transaction (stored functions always run in a transaction) in our example:

   1 CREATE OR REPLACE FUNCTION "public"."for_update_test" (
   2 )
   3 RETURNS integer AS
   4 $body$
   5 DECLARE
   6 
   7         id_query INTEGER;
   8 
   9 BEGIN
  10 
  11         RAISE NOTICE 'START AT: %',timeofday()::timestamp;
  12 
  13         SELECT id FROM example LIMIT 1 INTO id_query FOR UPDATE;
  14 
  15         RAISE NOTICE 'SELECT PERFORMED AT: %',timeofday()::timestamp;
  16 
  17         PERFORM pg_sleep(50);
  18 
  19         RAISE NOTICE 'END AT: %',timeofday()::timestamp;
  20 
  21         RETURN 0;
  22 END;
  23 $body$
  24 LANGUAGE 'plpgsql'
  25 VOLATILE
  26 CALLED ON NULL INPUT
  27 SECURITY INVOKER
  28 COST 100;

Then run the function with Select for_update_test(); inside a SQL prompt of pgAdminIII or any other environment.

After something like 25 seconds make another Select for_update_test(); in a second prompt as I did in the following:

(Please click on the image to see full size)

In the left prompt you can see that the SELECT FOR UPDATE is immediately performed after the transaction begins.

In the right prompt you can see that the SELECT FOR UPDATE is performed only after the first transaction ends.

So, actually the SELECT FOR UPDATE locks the only row of our table.

If you want you can change the SELECT FOR UPDATE adding the NOWAIT clause:

SELECT id FROM example LIMIT 1 INTO id_query FOR UPDATE NOWAIT;

and run the small experiment again. In this case the second function call will return an error because the NOWAIT clause does exactly what its name suggests: it doesn't wait if it encounters a locked row.

In the function I used the instruction timeofday()::timestamp because the NOW() function or the statement CURRENT_TIMESTAMP always return the same Date Time if used inside a transaction. More on this here http://kaiv.wordpress.com/2007/11/02/getting-current-time-inside-a-transaction/