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