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

100% CPU at concurent access

From: "Sabin Coanda" <sabin(dot)coanda(at)deuromedia(dot)ro>
To: pgsql-sql(at)postgresql(dot)org
Subject: 100% CPU at concurent access
Date: 2008-10-08 16:56:13
Message-ID: gciomv$pfu$1@news.hub.org (view raw or flat)
Thread:
Lists: pgsql-sql
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





Responses

pgsql-sql by date

Next:From: Tom LaneDate: 2008-10-08 23:13:21
Subject: Re: 100% CPU at concurent access
Previous:From: Louis-David MitterrandDate: 2008-10-08 09:25:10
Subject: Re: many-to-many relationship

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