Обсуждение: 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

Mladen Gogala <mladen.gogala@vmsinfo.com> writes:
> 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.

You might want to go reread this:
http://www.postgresql.org/docs/9.0/static/transaction-iso.html#XACT-READ-COMMITTED
If you don't like that behavior, you might want SERIALIZABLE mode
instead.

            regards, tom lane

Tom Lane wrote:
> Mladen Gogala <mladen.gogala@vmsinfo.com> writes:
>
>> 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.
>>
>
> You might want to go reread this:
> http://www.postgresql.org/docs/9.0/static/transaction-iso.html#XACT-READ-COMMITTED
> If you don't like that behavior, you might want SERIALIZABLE mode
> instead.
>
>             regards, tom lane
>
No, it wasn't about liking or not liking, I was only trying to get to
the bottom of this behavior. In particular, I translated the phrase "
The search condition of the command (the WHERE clause) is re-evaluated "
as the transaction restart and have fully expected the triggers to fire
twice, which didn't happen.  I am comparing Postgres to Oracle, to find
out where should I expect different behavior and where should I expect
exactly the same behavior. So far, I must say, the changes aren't too
big. One of the biggest is the lack of the %ROWCOUNT attribute with
cursors and a different set of exceptions. Also, there is no
DBMS_OUTPUT, I have to use RAISE NOTICE, which feels strange but does
the trick. As a DBA, I should be able to help developers when they do
run into problems. And they will.

--

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

On Tue, Oct 12, 2010 at 1:59 PM, Mladen Gogala
<mladen.gogala@vmsinfo.com> wrote:

> Also, there is no DBMS_OUTPUT, I have to use RAISE
> NOTICE, which feels strange but does the trick.

I don't know much about Oracle or DBMS_OUTPUT, but would the LISTEN
and NOTIFY mechanism not work for this?

http://www.postgresql.org/docs/9.0/interactive/sql-listen.html
http://www.postgresql.org/docs/9.0/interactive/sql-notify.html

--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug