Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2015
    Posts
    3

    Unanswered: One statement is blocking other statement (two different sessions)

    Hi,

    I would need help to understand what's going wrong internally and why postgres is locking two different statements which is not linked at all.

    Postgres Version: PostgreSQL 9.3.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-4), 64-bit

    Example:

    User 1: First Session
    Code:
    CREATE TABLE  user1_tb1 as select user_id,count(1) FROM Test1 group by user_id;

    User 1: Second Session

    Code:
    CREATE TABLE user1_tbl2 as select * FROM Test10;
    Second statement is waiting for first session's statement to finish. There is no link between two tables.

    Code:
    SELECT blocked_locks.pid     AS blocked_pid,
             blocked_activity.usename  AS blocked_user,
             blocking_locks.pid     AS blocking_pid,
             blocking_activity.usename AS blocking_user,
             blocked_activity.query    AS blocked_statement,
             blocking_activity.query   AS blocking_statement,
             blocking_locks.mode   AS blocking_mode
       FROM  pg_catalog.pg_locks         blocked_locks
        JOIN pg_catalog.pg_stat_activity blocked_activity  ON blocked_activity.pid = blocked_locks.pid
        JOIN pg_catalog.pg_locks         blocking_locks 
            ON blocking_locks.locktype = blocked_locks.locktype
            AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
            AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
            AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
            AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
            AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
            AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
            AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
            AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
            AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
            AND blocking_locks.pid != blocked_locks.pid
        JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
       WHERE NOT blocked_locks.granted;
    Output
    =====
    blocked_user : user2
    blocking_user : user1
    blocked_Statement : CREATE TABLE user1_tbl2 as select * FROM Test10;
    blocking statement: CREATE TABLE user1_tb1 as select user_id,count(1) FROM Test1 group by user_id;
    blocking mode: AccessShareLock


    If we don't use GROUP BY clause in first statement then it doesn't blocks second statement. Is this something related to temporary files/sorting?. There is no default temp_tablespace is specified at database level.

    Any ideas what's going wrong?

    Thanks
    Mohammad
    Last edited by mamajiwala; 03-23-15 at 19:04.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Both statements are creating a table with the same name. As it is not allowed to have two tables with the same name, the second has to wait until it knows whether the first commits or rolls back.

    If the first one commits, the second one results in an error. If the first one does a rollback, the second one succeeds.

    It's the same as two statements inserting the same value for a primary key: the database needs to hold the second statement until it's clear if will violate the primary key constraint.

    Please learn to properly format SQL statements when posting using [code] tags
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  3. #3
    Join Date
    Mar 2015
    Posts
    3
    Thanks for your reply. It was my typo mistake. Actually in second session, table name is different.

    User 1: Second Session
    CREATE TABLE user1_tbl2 as select * FROM Test10;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •