Обсуждение: 100% CPU at concurent access

Hi there,

I'm trying to solve the concurrent access in my database, but I found some 
problems.

I use "PostgreSQL 8.2.4 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 
3.4.2 (mingw-special)";

I create the following scenario:
- use an empty database
- create the following table:
               CREATE TABLE "TestTable"               (                 "ID" integer NOT NULL,
CONSTRAINT"TestTable_pkey" PRIMARY KEY ("ID")               )               WITHOUT OIDS;               ALTER TABLE
"TestTable"OWNER TO postgres;
 

- add a row in the table with
               INSERT INTO "TestTable" VALUES ( 1000 );

- create the following functions:
               CREATE OR REPLACE FUNCTION "TestProcInner"()                 RETURNS integer AS               $BODY$
         DECLARE                       Loops int4 = 10;               BEGIN                       FOR i IN 0..Loops
LOOP                              RAISE NOTICE '%',i;                               UPDATE "TestTable" SET "ID" = i;
                          PERFORM pg_sleep(1);                       END LOOP;
 
                       RAISE NOTICE 'SUCCEEDED';                       RETURN 0;
               EXCEPTION                   WHEN serialization_failure THEN                       RAISE NOTICE
'FAILED';
                       RETURN 1;               END;               $BODY$                 LANGUAGE 'plpgsql' VOLATILE;
           ALTER FUNCTION "TestProcInner"() OWNER TO postgres;
 
       and
               CREATE OR REPLACE FUNCTION "TestProcOuter"()                 RETURNS integer AS               $BODY$
         DECLARE                       Loops int4 := 1;               BEGIN                       LOOP
            RAISE NOTICE 'TestProcOuter: % loop', Loops;                               IF 0 = "TestProcInner"() THEN
                                  EXIT; -- LOOP                               END IF;
Loops= Loops + 1;                       END LOOP;
 
                       RETURN 0;               END;               $BODY$                 LANGUAGE 'plpgsql' VOLATILE;
           ALTER FUNCTION "TestProcOuter"() OWNER TO postgres;
 


I use the following procedure to check when the concurrency access occures:
- open two query windows in pgAdmin
- add the following script in the both windows:
               BEGIN TRANSACTION;               SELECT "TestProcInner"();               COMMIT;

- run the script in the 1st window
- run the script in the 2nd window
- check the results:       - the script in the 1st window commit the transaction               and write the log
message'SUCCEEDED'       - the script from the 2nd window catch an exception               and write the log message
'FAILED'


Then I try to use the following procedure to catch the concurrency access       occurence and retry until both scripts
succeed:
- open two query windows in pgAdmin
- add the following script in the both windows:
               BEGIN TRANSACTION;               SELECT "TestProcOuter"();               COMMIT;

- run the script in the 1st window
- run the script in the 2nd window
- the Postgres begins to CONSUME 100% CPU, and LOCKS until I cancel the 
connection       from other pgAdmin session
- after a few second the first window finishes with 'SUCCEEDED'
- the second window writes:
               ERROR: canceling statement due to user request

Could somebody tell me why the procedure doesn't work and the CPU is used 
100%, please ?

TIA,
Sabin

"Sabin Coanda" <sabin.coanda@deuromedia.ro> writes:
> Then I try to use the following procedure to catch the concurrency access
>         occurence and retry until both scripts succeed:

What makes you think they ever will succeed?  Once one of these guys has
hit a failure, you've got a tight loop of retrying the same command and
getting the same failure.
        regards, tom lane

Hi Tom,

Well, I thought the connection with the failed transaction checks in a loop 
until the succeeded transaction will finish, and then it will succeeded as 
well.

However, would you suggest me a code for "TestProcOuter" that works and 
fulfils my desire, please ? :)


Thanx,
Sabin 

I find the problem is in my outer procedure, because it has no sleep there, 
and I change it calling pg_sleep:

-- Function: "TestProcOuter"()

-- DROP FUNCTION "TestProcOuter"();

CREATE OR REPLACE FUNCTION "TestProcOuter"() RETURNS integer AS
$BODY$
DECLARE       Loops int4 := 1;
BEGIN       LOOP               RAISE NOTICE 'TestProcOuter: % loop', Loops;               IF 0 = "TestProcInner"() THEN
                     EXIT; -- LOOP               END IF;               Loops = Loops + 1;               PERFORM
pg_sleep(4);      END LOOP;
 
       RETURN 0;
END;
$BODY$ LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION "TestProcOuter"() OWNER TO postgres;

With this change, I found the first session succeeds, the CPU is not rised 
anymore, but the second session doesn't succeed even after the first one 
finish successfully.

It fails forever.

Why ? What have I make to succeed ?

TIA,
Sabin