hawq-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Grant Krieger <GKrie...@mrpg.com>
Subject RE: Lock is retained on a table
Date Mon, 25 Sep 2017 09:15:48 GMT
https://issues.apache.org/jira/browse/HAWQ-1530

Done

Thanks

From: Yi JIN [mailto:jinyi.apache@gmail.com]
Sent: 21 September 2017 10:51 PM
To: user@hawq.incubator.apache.org
Subject: Re: Lock is retained on a table

BTW, Grant, do you think it suitable to move this issue to JIRA for discussing and tracking?

Best,
Yi

On Fri, Sep 22, 2017 at 6:46 AM, Yi JIN <jinyi.apache@gmail.com<mailto:jinyi.apache@gmail.com>>
wrote:
Hi Grant,

Did you check on master if the working process exists? For example, ps -ef | grep postgres
| grep 393937

If you can find the pid in pg_locks, I think the process should still be there.  So maybe
you can pstack that process to check the stack, maybe we can find more hints.

Best,
Yi

On Thu, Sep 21, 2017 at 7:50 PM, Grant Krieger <GKrieger@mrpg.com<mailto:GKrieger@mrpg.com>>
wrote:
Hi All,

Just to add.

It appears lock still held but there are no active querys running on table

select * from pg_stat_activity
where procpid = 335492


1075919                edw_performance3       335492  342         10           gpadmin   
          drop table public.test_prod_dim3                true       2017/09/21 10:51:22 AM
              2017/09/21 8:58:55 AM  10.1.3.201            61257                    2017/09/21
10:51:22 AM       false

select * from pg_stat_activity
where current_query like '%test_prod_dim3%'


1075919                edw_performance3       335492  342         10           gpadmin   
          drop table public.test_prod_dim3                                               
                                                true       2017/09/21 10:51:22 AM        
      2017/09/21 8:58:55 AM                10.1.3.201            61257                   
2017/09/21 10:51:22 AM               false
1075919                edw_performance3       376465  550         10           gpadmin   
          select * from pg_stat_activity where current_query like '%test_prod_dim3%'     
         false      2017/09/21 11:24:51 AM               2017/09/21 10:14:33 AM          
     10.1.3.201            62030                    2017/09/21 11:24:51 AM               false

The problem seems to exist when we have pids in pg_locks which don’t exist in pg_stat_activity.
See query below

select b.relname,a.* from pg_locks a
join pg_class b
on a.relation = b.relfilenode
where pid not in
(
select procpid from pg_stat_activity
)
and b.relname = 'test_prod_dim3'

test_prod_dim3               relation 1075919                1710089                (null)
    (null)     (null)     (null)     (null)     (null)                109830069          
393937  AccessShareLock              true       647         false      -10000


So this dont work

SELECT pg_terminate_backend(393937)


Thanks


From: Grant Krieger
Sent: 21 September 2017 11:01 AM
To: user@hawq.incubator.apache.org<mailto:user@hawq.incubator.apache.org>
Subject: RE: Lock is retained on a table

Hi Guys,

Thanks all for your help.

I have scanned the log and there is no fatal message.

On simulation via psql via putty there seems to be no problem however it appears to exist
when connecting via JDBC. If I close the JDBC client via Aqua data studio on the last select
statement the lock remains. Maybe it’s a JDBC setting I need to change.

Below is a test script which loads data then a test query

--- loading dummy data

DROP
      TABLE if EXISTS PUBLIC.test_prod_dim3;
CREATE
      TABLE PUBLIC.test_prod_dim3
      (
            productid int4 NULL,
            description text NULL
      )
      WITH
      (
            APPENDONLY = TRUE,
            COMPRESSTYPE = SNAPPY,
            OIDS = FALSE
      )
      DISTRIBUTED RANDOMLY;
INSERT
INTO
      PUBLIC.test_prod_dim3
SELECT
      1       AS productid,
      '1 desc' AS description
FROM
      generate_series(1,100000000);
DROP
      TABLE if EXISTS PUBLIC.test_table_fact3;
CREATE
      TABLE PUBLIC.test_table_fact3
      (
            productid int4 NULL,
            value1 int4 NULL
      )
      WITH
      (
            APPENDONLY = TRUE,
            COMPRESSTYPE = SNAPPY,
            OIDS = FALSE
      )
      DISTRIBUTED RANDOMLY;
INSERT
INTO
      PUBLIC.test_table_fact3
SELECT
      1 AS productid,
      1 AS value1
FROM
      generate_series(1,100000000);
      --End loading dummy data


      --Starting test query. Note this only works when I close JDBC client with CTRL ALT DELETE
when this step is running

SELECT
      *
FROM
      (     SELECT
                  a.*,
                  b.description
            FROM
                  PUBLIC.test_table_fact3 a
                  JOIN PUBLIC.test_prod_dim3 b
                  ON a.productid = b.productid ) AS q LIMIT 10000;


--Then in a new session

drop table public.test_prod_dim3;

select * from pg_stat_activity
where procpid = 335492

1075919     edw_performance3  335492      342   10    gpadmin     drop table public.test_prod_dim3
  true  2017/09/21 10:51:22 AM  2017/09/21 8:58:55 AM   10.1.3.201      61257       2017/09/21
10:51:22 AM  false

select * from pg_locks
where pid = 335492

relation    1075919     1710089     (null)      (null)      (null)      (null)      (null)
     (null)      109830156   335492      AccessExclusiveLock     false 342      false -10000
transactionid     (null)      (null)      (null)      (null)      109830156   (null)     
(null)      (null)      109830156   335492      ExclusiveLock     true  342      false -10000
relation    1075919     1259  (null)      (null)      (null)      (null)      (null)     
(null)      109830156   335492      RowExclusiveLock  true  342   false -10000
relation    1075919     2608  (null)      (null)      (null)      (null)      (null)     
(null)      109830156   335492      RowExclusiveLock  true  342   false -10000
relation    1075919     1247  (null)      (null)      (null)      (null)      (null)     
(null)      109830156   335492      RowExclusiveLock  true  342   false -10000



From: Shubham Sharma [mailto:ssharma@pivotal.io]
Sent: 20 September 2017 11:52 PM
To: user@hawq.incubator.apache.org<mailto:user@hawq.incubator.apache.org>
Subject: Re: Lock is retained on a table

Hi Grant,

Will it be feasible for you to share a reproducible case where you see this behavior. I have
tried the following and don't see the behavior as you decribed


[gpadmin@hdp3 ~]$ cat simple_select_query.sql

select * from t1

psql -c "select * from pg_locks";

   locktype    | database | relation | page | tuple | transactionid | classid | objid | objsubid
| transaction |  pid   |      mode       | granted | mppsessionid | mppiswriter | gp_segment_id

---------------+----------+----------+------+-------+---------------+---------+-------+----------+-------------+--------+-----------------+---------+--------------+-------------+---------------

 transactionid |          |          |      |       |       1639719 |         |       |  
       |     1639719 | 748151 | ExclusiveLock   | t       |         1720 | f           | 
      -10000

 relation      |  1311369 |    10335 |      |       |               |         |       |  
       |     1639719 | 748151 | AccessShareLock | t       |         1720 | f           | 
      -10000

(2 rows)



[gpadmin@hdp3 ~]$ nohup psql -f simple_select_query.sql &

[1] 748153

[gpadmin@hdp3 ~]$ kill -9 748153



[gpadmin@hdp3 ~]$ psql -c "select * from pg_locks";

   locktype    | database | relation | page | tuple | transactionid | classid | objid | objsubid
| transaction |  pid   |      mode       | granted | mppsessionid | mppiswriter | gp_segment_id

---------------+----------+----------+------+-------+---------------+---------+-------+----------+-------------+--------+-----------------+---------+--------------+-------------+---------------

 relation      |  1311369 |    10335 |      |       |               |         |       |  
       |     1639725 | 748410 | AccessShareLock | t       |         1723 | f           | 
      -10000

 transactionid |          |          |      |       |       1639725 |         |       |  
       |     1639725 | 748410 | ExclusiveLock   | t       |         1723 | f           | 
      -10000

(2 rows)



I don't see a dangling exclusive lock. Is that something similar what you mentioned in your
approach.

On Wed, Sep 20, 2017 at 6:10 AM, Yi JIN <jinyi.apache@gmail.com<mailto:jinyi.apache@gmail.com>>
wrote:
Hi Grant,

Most likely it is a bug. Can you have a look the master log files, to check if there are some
FATAL logs? And could you extract those FATAL lines out ?

Best,
Yi

On Wed, Sep 20, 2017 at 10:18 PM, Grant Krieger <GKrieger@mrpg.com<mailto:GKrieger@mrpg.com>>
wrote:
Hi ,

I have come across a funny in hawq 2.2.0.0. If we do a long running simple select only query
on a table and we illegally terminate the client session (Just close the query tool window
without cancelling) an exclusive lock is held on the underlying table until a server restart
is done.
This is even when the query has completed on the server.
The terminate backend or Linux kill has no effect

Any ideas?

Thanks

Grant Krieger


[Mr PriceGroup Limited]


Grant Krieger
Portfolio Lead
mrp IT
T: +27313108478<tel:+27%2031%20310%208478>
E: gkrieger@mrpg.com<mailto:gkrieger@mrpg.com>
W: http://www.mrpricegroup.com



The information contained in this communication from gkrieger@mrpg.com<mailto:gkrieger@mrpg.com>
is confidential and may be legally privileged. This email is intended solely for use by the
addressee and others authorized to receive it, and should not be read if delivered in error.
If you are not the addressee you are hereby notified that any disclosure, copying, distribution
or action taken or omitted in reliance of the contents of this information is strictly prohibited
and may be unlawful. Please notify the sender immediately if the e-mail has unintentionally
reached you. The email legal notice that governs the transmission of this electronic communication
is available online at Mr Price Group Limited (http://www.mrpricegroup.com/LegalNotice.aspx).
If you cannot access the legal notice, kindly contact us on +27 800 21 2535 and a copy will
be provided to you.

Mr Price Group is an Authorised Financial Services and Credit Provider: FSP31450 and NCRCP46.

PPlease consider the environment before printing this email.





--
Regards,
Shubham Sharma
Staff Customer Engineer
Pivotal Global Support Services
ssharma@pivotal.io<mailto:ssharma@pivotal.io>
Direct Tel: +1(510)-304-8201<tel:(510)%20304-8201>
Office Hours: Mon-Fri 9:00 am to 5:00 pm PDT
Out of Office Hours Contact +1 877-477-2269<tel:(877)%20477-2269>


Mime
View raw message