Skip site navigation (1) Skip section navigation (2)

Concurrency

From: Mladen Gogala <mladen(dot)gogala(at)vmsinfo(dot)com>
To: "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>
Subject: Concurrency
Date: 2010-10-12 20:21:02
Message-ID: 4CB4C32E.9040109@vmsinfo.com (view raw or flat)
Thread:
Lists: pgsql-novice
I did the following in a 9.01 database:

    CREATE TABLE t
    (  x integer,
       y integer,
       z integer);


    CREATE TRIGGER t_buffer
      BEFORE UPDATE
      ON t
      FOR EACH ROW
      EXECUTE PROCEDURE t_buffer();


The trigger function is extremely simple and it only displays the values:

    CREATE OR REPLACE FUNCTION t_buffer()
      RETURNS trigger AS
    $BODY$
    begin
    raise notice 'old.x = ',OLD.x,',old.y=',OLD.y;
    raise notice 'old.x = ',NEW.x,',old.y=',NEW.y;
    return NEW;
    end;
    $BODY$
      LANGUAGE plpgsql VOLATILE
      COST 100;

I inserted the triple (1,1,1) into the table and then opened 2 sessions. 
The 1st session executes like this:

    BEGIN
    Time: 0.108 ms
    scott=# update t set x = x+1;
    NOTICE:  old.x = 1,old.y=1
    NOTICE:  new.x = 2,new.y=1
    UPDATE 1
    Time: 0.683 ms

Here I waited and opened another session.


    scott=# commit;
    COMMIT
    Time: 16.187 ms

The problem occurs when I executed the 2nd transaction:

    BEGIN
    Time: 0.117 ms
    scott=# update t set x = x+2 where x > 0;

The transaction was blocked until I didn't commit the 1st transaction.

    NOTICE:  old.x = 2,old.y=1
    NOTICE:  new.x = 4,new.y=1
    UPDATE 1
    Time: 7780.669 ms
    scott=# commit;
    COMMIT
    Time: 28.557 ms



Where is the problem? The problem lies in the fact that the 2nd 
transaction should have only seen the changes committed before it has 
begun, ie, x=1.  The 1st transaction has committed AFTER the 2nd 
transaction has begun but the 2nd transaction still sees x=2. The only 
logical explanation is that the 2nd transaction has restarted when it 
discovered the changed block, but the trigger did not fire twice. 
Anybody care to sched some light on this? If that looks familiar, here 
is the motivation for such an exotic test:

http://tkyte.blogspot.com/2005/08/part-ii-seeing-restart.html


The result is exactly the same, but the trigger behavior isn't.

-- 
 
Mladen Gogala 
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com 
The Leader in Integrated Media Intelligence Solutions




Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2010-10-12 20:42:50
Subject: Re: Concurrency
Previous:From: Tom LaneDate: 2010-10-12 19:20:43
Subject: Re: NonNullValue() error in 8.4

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group