Обсуждение: Deadlock occur while creating new table to be used in partition.

Currently, I have a stored procedure(get_existing_or_create_lot), which will be called by 2 or more processes
simultaneously.

Every process will have a unique lot name. What the store procedure does it

1) Insert lot name into "lot" table. A unique lot id will be returned after insertion into "lot" table.

2) Check if unit_{id} table does exist. For example, if the returned lot id is 14, PostgreSQL will check whether
"unit_14"table does exist. If no, "CREATE TABLE unit_14..." will be executed. 


The stored procedure code is as follow :


CREATE OR REPLACE FUNCTION get_existing_or_create_lot(text)
  RETURNS TABLE(_lot_id int) AS
$BODY$DECLARE
    _param_name ALIAS FOR $1;

    _lot lot;

    unit_table_index int;
    unit_table_name text;

BEGIN
    -- Insert lot name into lot table.
    INSERT INTO lot(name) VALUES(_param_name) RETURNING  * INTO _lot;

    unit_table_index = _lot.lot_id;
    unit_table_name = 'unit_' || unit_table_index;

    IF NOT EXISTS(SELECT * FROM information_schema.tables WHERE table_name = unit_table_name) THEN
        EXECUTE 'CREATE TABLE ' || quote_ident(unit_table_name) || '
        (
          unit_id serial NOT NULL,
          fk_lot_id int NOT NULL,
          CHECK (fk_lot_id = ' || (unit_table_index) || '),
          CONSTRAINT pk_unit_' || unit_table_index || '_id PRIMARY KEY (unit_id),
          CONSTRAINT fk_lot_' || unit_table_index || '_id FOREIGN KEY (fk_lot_id) REFERENCES lot (lot_id) MATCH SIMPLE
ONUPDATE NO ACTION ON DELETE CASCADE     
        ) INHERITS (unit);';

        EXECUTE 'CREATE INDEX fk_lot_' || unit_table_index || '_id_idx ON ' || quote_ident(unit_table_name) ||
'(fk_lot_id);';      
    END IF;





Unfortunately, I get the run time error ;

2010-04-26 13:28:28 MYTERROR:  deadlock detected
2010-04-26 13:28:28 MYTDETAIL:  Process 436 waits for AccessExclusiveLock on relation 46757 of database 46753; blocked
byprocess 4060.     
    Process 4060 waits for AccessExclusiveLock on relation 46757 of database 46753; blocked by process 436.
    Process 436: SELECT * FROM get_existing_or_create_lot('Testing02')
    Process 4060: SELECT * FROM get_existing_or_create_lot('Testing02')
2010-04-26 13:28:28 MYTHINT:  See server log for query details.
2010-04-26 13:28:28 MYTCONTEXT:  SQL statement "CREATE TABLE unit_16
            (
              unit_id serial NOT NULL,
              fk_lot_id int NOT NULL,
              CHECK (fk_lot_id = 16),
              CONSTRAINT pk_unit_16_id PRIMARY KEY (unit_id),
              CONSTRAINT fk_lot_16_id FOREIGN KEY (fk_lot_id) REFERENCES lot (lot_id) MATCH SIMPLE ON UPDATE NO ACTION
ONDELETE CASCADE     
            ) INHERITS (unit);"
    PL/pgSQL function "get_existing_or_create_lot" line 39 at EXECUTE statement
2010-04-26 13:28:28 MYTSTATEMENT:  SELECT * FROM get_existing_or_create_lot('Testing02')



May I know why does deadlock happen? How can I avoid?

Thanks and Regards
Yan Cheng CHEOK

Yan Cheng CHEOK <yccheok@yahoo.com> writes:
> Currently, I have a stored procedure(get_existing_or_create_lot), which will be called by 2 or more processes
simultaneously.
> Every process will have a unique lot name. What the store procedure does it

> 1) Insert lot name into "lot" table. A unique lot id will be returned after insertion into "lot" table.

> 2) Check if unit_{id} table does exist. For example, if the returned lot id is 14, PostgreSQL will check whether
"unit_14"table does exist. If no, "CREATE TABLE unit_14..." will be executed. 

> Unfortunately, I get the run time error ;
> 2010-04-26 13:28:28 MYTERROR:  deadlock detected

The reason for the error is probably that establishing the FK reference
to table "lot" requires an exclusive lock on "lot", so each occurrence
of this creation will serialize on that, in addition to anything else
it might be locking.

My opinion is that you're shooting yourself in the foot with a poorly
chosen database layout.  Forget all the subtables and just have one
big unit table.  It'll be far simpler and probably perform better too.

            regards, tom lane

I post a complete code for this stored procedure, so that I won't miss out any hint which may cause this deadlock to
occur.


CREATE OR REPLACE FUNCTION get_existing_or_create_lot(text, text, text[], text[])
  RETURNS TABLE(_lot_id int, _start_timestamp double precision, _end_timestamp double precision, _name text,
_applicationtext, _param_type text, _param_value text) AS 
$BODY$DECLARE
    _param_name ALIAS FOR $1;
    _param_application ALIAS FOR $2;
    _param_types ALIAS FOR $3;
    _param_values ALIAS FOR $4;
    i int;
    _param_type_id int;
    _lot lot;

    unit_table_index int;
    unit_table_name text;
    measurement_table_index int;
    measurement_table_name text;
BEGIN
    -- Parameters validation.
    IF array_upper(_param_types, 1) != array_upper(_param_values, 1) THEN
        RAISE EXCEPTION 'Inconsistency in array size';
    END IF;

    -- Find an existing lot.
    SELECT INTO _lot * FROM lot WHERE name = _param_name AND application = _param_application;

    -- Fall into creation code block.
    IF FOUND THEN
        PERFORM update_or_insert_params(_lot.lot_id, _param_types, _param_values);
        RETURN QUERY EXECUTE 'SELECT * FROM get_lot_by_id($1)' USING _lot.lot_id;
        RETURN;
    END IF;

    INSERT INTO lot(end_timestamp, name, application) VALUES(NULL, _param_name, _param_application) RETURNING  * INTO
_lot;
    PERFORM update_or_insert_params(_lot.lot_id, _param_types, _param_values);

    unit_table_index = _lot.lot_id;
    unit_table_name = 'unit_' || unit_table_index;
    measurement_table_index = _lot.lot_id;
    measurement_table_name = 'measurement_' || measurement_table_index;

    IF NOT EXISTS(SELECT * FROM information_schema.tables WHERE table_name = unit_table_name) THEN
        EXECUTE 'CREATE TABLE ' || quote_ident(unit_table_name) || '
        (
          unit_id serial NOT NULL,
          fk_lot_id int NOT NULL,
          CHECK (fk_lot_id = ' || (unit_table_index) || '),
          CONSTRAINT pk_unit_' || unit_table_index || '_id PRIMARY KEY (unit_id),
          CONSTRAINT fk_lot_' || unit_table_index || '_id FOREIGN KEY (fk_lot_id) REFERENCES lot (lot_id) MATCH SIMPLE
ONUPDATE NO ACTION ON DELETE CASCADE     
        ) INHERITS (unit);';

        EXECUTE 'CREATE INDEX fk_lot_' || unit_table_index || '_id_idx ON ' || quote_ident(unit_table_name) ||
'(fk_lot_id);';      
    END IF;

    IF NOT EXISTS(SELECT * FROM information_schema.tables WHERE table_name = measurement_table_name) THEN
        EXECUTE 'CREATE TABLE ' || quote_ident(measurement_table_name) || '
        (
          measurement_id serial NOT NULL,
          fk_unit_id int NOT NULL,
          measurement_type text NOT NULL,
          measurement_unit text NOT NULL,
          lot_id int NOT NULL,
          CHECK (lot_id = ' || (measurement_table_index) || '),


          CONSTRAINT pk_measurement_' || measurement_table_index || '_id PRIMARY KEY (measurement_id),
          CONSTRAINT fk_unit_' || measurement_table_index || '_id FOREIGN KEY (fk_unit_id)
              REFERENCES unit_' || measurement_table_index || ' (unit_id) MATCH SIMPLE
              ON UPDATE NO ACTION ON DELETE CASCADE
        ) INHERITS (measurement);';

        EXECUTE 'CREATE INDEX idx_fk_unit_' || measurement_table_index || '_id
          ON ' || quote_ident(measurement_table_name) || ' USING btree
          (fk_unit_id);';

        EXECUTE 'CREATE INDEX idx_measurement_value_' || measurement_table_index || '
          ON ' || quote_ident(measurement_table_name) || ' USING btree
          (value) WHERE value IS NULL;';

        EXECUTE 'CREATE INDEX idx_lot_' || measurement_table_index || ' ON ' || quote_ident(measurement_table_name) ||
'(lot_id);'; 
    END IF;

    RETURN QUERY EXECUTE 'SELECT * FROM get_lot_by_id($1)' USING _lot.lot_id;
END;$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION get_existing_or_create_lot(text, text, text[], text[]) OWNER TO postgres;



Thanks and Regards
Yan Cheng CHEOK


--- On Mon, 4/26/10, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> From: Tom Lane <tgl@sss.pgh.pa.us>
> Subject: Re: [GENERAL] Deadlock occur while creating new table to be used in partition.
> To: "Yan Cheng CHEOK" <yccheok@yahoo.com>
> Cc: pgsql-general@postgresql.org
> Date: Monday, April 26, 2010, 2:04 PM
> Yan Cheng CHEOK <yccheok@yahoo.com>
> writes:
> > Currently, I have a stored
> procedure(get_existing_or_create_lot), which will be called
> by 2 or more processes simultaneously.
> > Every process will have a unique lot name. What the
> store procedure does it
>
> > 1) Insert lot name into "lot" table. A unique lot id
> will be returned after insertion into "lot" table.
>
> > 2) Check if unit_{id} table does exist. For example,
> if the returned lot id is 14, PostgreSQL will check whether
> "unit_14" table does exist. If no, "CREATE TABLE unit_14..."
> will be executed.
>
> > Unfortunately, I get the run time error ;
> > 2010-04-26 13:28:28 MYTERROR:  deadlock
> detected   
>
> The reason for the error is probably that establishing the
> FK reference
> to table "lot" requires an exclusive lock on "lot", so each
> occurrence
> of this creation will serialize on that, in addition to
> anything else
> it might be locking.
>
> My opinion is that you're shooting yourself in the foot
> with a poorly
> chosen database layout.  Forget all the subtables and
> just have one
> big unit table.  It'll be far simpler and probably
> perform better too.
>
>            
> regards, tom lane
>

By the way, how I can diagnostic, what is

1) relation 46757
2) database 46753

Thanks and Regards
Yan Cheng CHEOK


--- On Mon, 4/26/10, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> From: Tom Lane <tgl@sss.pgh.pa.us>
> Subject: Re: [GENERAL] Deadlock occur while creating new table to be used in partition.
> To: "Yan Cheng CHEOK" <yccheok@yahoo.com>
> Cc: pgsql-general@postgresql.org
> Date: Monday, April 26, 2010, 2:04 PM
> Yan Cheng CHEOK <yccheok@yahoo.com>
> writes:
> > Currently, I have a stored
> procedure(get_existing_or_create_lot), which will be called
> by 2 or more processes simultaneously.
> > Every process will have a unique lot name. What the
> store procedure does it
>
> > 1) Insert lot name into "lot" table. A unique lot id
> will be returned after insertion into "lot" table.
>
> > 2) Check if unit_{id} table does exist. For example,
> if the returned lot id is 14, PostgreSQL will check whether
> "unit_14" table does exist. If no, "CREATE TABLE unit_14..."
> will be executed.
>
> > Unfortunately, I get the run time error ;
> > 2010-04-26 13:28:28 MYTERROR:  deadlock
> detected   
>
> The reason for the error is probably that establishing the
> FK reference
> to table "lot" requires an exclusive lock on "lot", so each
> occurrence
> of this creation will serialize on that, in addition to
> anything else
> it might be locking.
>
> My opinion is that you're shooting yourself in the foot
> with a poorly
> chosen database layout.  Forget all the subtables and
> just have one
> big unit table.  It'll be far simpler and probably
> perform better too.
>
>            
> regards, tom lane
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

I wish to have simpler solution too :)

The reason I want to use "complicated" table partition way is that :

(1) I have a few thousands lot's row
(2) Each lot will associate with millions row of unit.
(3) If I use a single unit tables, my unit tables will have billions of row :o

The design works fine (requirement/ performance/ reliability...). Only until the last minute we put it run under
multi-threadedenvironment before shipment, I shoot on my own foot :) 

As this stored procedure isn't being called frequent (and it is not the performance bottle neck), I was thinking to
havea "hack" solution. 

Having a mutual exclusive (process level) protection around the whole function. Each time, only allow one process to
executeget_existing_or_create_lot. 

In Windows programming world, we call it mutex. But in PostgreSQL, what I shall use? LOCK command? But LOCK command
requireda dummy table. 

FUNCTION get_existing_or_create_lot
    BEGIN
    LOCK dummy_table
    ...
    ...
    END;$BODY$


Thanks and Regards
Yan Cheng CHEOK


--- On Mon, 4/26/10, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> From: Tom Lane <tgl@sss.pgh.pa.us>
> Subject: Re: [GENERAL] Deadlock occur while creating new table to be used in partition.
> To: "Yan Cheng CHEOK" <yccheok@yahoo.com>
> Cc: pgsql-general@postgresql.org
> Date: Monday, April 26, 2010, 2:04 PM
> Yan Cheng CHEOK <yccheok@yahoo.com>
> writes:
> > Currently, I have a stored
> procedure(get_existing_or_create_lot), which will be called
> by 2 or more processes simultaneously.
> > Every process will have a unique lot name. What the
> store procedure does it
>
> > 1) Insert lot name into "lot" table. A unique lot id
> will be returned after insertion into "lot" table.
>
> > 2) Check if unit_{id} table does exist. For example,
> if the returned lot id is 14, PostgreSQL will check whether
> "unit_14" table does exist. If no, "CREATE TABLE unit_14..."
> will be executed.
>
> > Unfortunately, I get the run time error ;
> > 2010-04-26 13:28:28 MYTERROR:  deadlock
> detected   
>
> The reason for the error is probably that establishing the
> FK reference
> to table "lot" requires an exclusive lock on "lot", so each
> occurrence
> of this creation will serialize on that, in addition to
> anything else
> it might be locking.
>
> My opinion is that you're shooting yourself in the foot
> with a poorly
> chosen database layout.  Forget all the subtables and
> just have one
> big unit table.  It'll be far simpler and probably
> perform better too.
>
>            
> regards, tom lane
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>