hawq-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Yi JIN <jinyi.apa...@gmail.com>
Subject Re: Lock is retained on a table
Date Thu, 21 Sep 2017 20:46:58 GMT
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> 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
> *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 <ssharma@pivotal.io>]
> *Sent:* 20 September 2017 11:52 PM
> *To:* 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> 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> 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
>
>
>
> [image: Mr PriceGroup Limited]
>
> *Grant Krieger *
> *Portfolio Lead *
> mrp IT
> *T:* +27313108478 <+27%2031%20310%208478>
> *E:* gkrieger@mrpg.com
> *W:* http://www.mrpricegroup.com
>
>
>
> The information contained in this communication from 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
>
> Direct Tel: +1(510)-304-8201 <(510)%20304-8201>
>
> Office Hours: Mon-Fri 9:00 am to 5:00 pm PDT
>
> Out of Office Hours Contact +1 877-477-2269 <(877)%20477-2269>
>

Mime
View raw message