Concurrency

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