Thread: table lock when where clause uses unique constraing instead of primary key.

table lock when where clause uses unique constraing instead of primary key.

From

Jeff Amiel

Date:

04 November 2013, 20:06:39

PostgreSQL 9.2.4 on x86_64-pc-solaris2.10, compiled by gcc (GCC) 3.4.3 (csl-sol210-3_4-branch+sol_rpath), 64-bit
Have got an annoying scenario that has been creating issues for us for years….
Time to try to figure it out.
Essentially, we have a user table where we maintain username, id number, enabled/disabled state, etc.
When a user logs in successfully, we reset any failed login attempts on the user’s unique entry in this table.

CREATE TABLE user_profile
(
  user_id serial NOT NULL,
  username character varying(50) NOT NULL,
  login_attempts integer DEFAULT 0,
  …
  CONSTRAINT user_id PRIMARY KEY (user_id),
  CONSTRAINT name UNIQUE (username)
)

However - we often get “lock storms” where SOMEHOW, updates for individual users are causing all other updates to
‘lock’on each other. 
Eventually the storm abates (sometimes in seconds - sometimes in minutes)
See edited screen cap:
http://i.imgur.com/x4DdYaV.png
(PID 4899 just has a “where username = $1 cut off that you can’t see out to the right)
All updates are done using the username (unique constraint) instead of the primary key (the serial)
In retrospect, I suppose these queries should be using the primary key (9 year old code) but I am flummoxed as to how
theseupdates can be causing table? level locks. 
I’ve never been able to catch the lock information during one of these storms - but I assume it is a table level lock
causingthis. 
Thoughts?  Is this just ‘normal’ behavior that I am not expecting? (because postgres doesn’t know that the username is
aunique field)

Re: table lock when where clause uses unique constraing instead of primary key.

From

Rob Sargent

Date:

04 November 2013, 20:13:47

On 11/04/2013 10:06 AM, Jeff Amiel wrote:

PostgreSQL 9.2.4 on x86_64-pc-solaris2.10, compiled by gcc (GCC) 3.4.3 (csl-sol210-3_4-branch+sol_rpath), 64-bit
Have got an annoying scenario that has been creating issues for us for years….
Time to try to figure it out.
Essentially, we have a user table where we maintain username, id number, enabled/disabled state, etc.
When a user logs in successfully, we reset any failed login attempts on the user’s unique entry in this table.

CREATE TABLE user_profile
(
  user_id serial NOT NULL,
  username character varying(50) NOT NULL, 
  login_attempts integer DEFAULT 0,
  …
  CONSTRAINT user_id PRIMARY KEY (user_id),
  CONSTRAINT name UNIQUE (username)
)

However - we often get “lock storms” where SOMEHOW, updates for individual users are causing all other updates to ‘lock’ on each other.
Eventually the storm abates (sometimes in seconds - sometimes in minutes)
See edited screen cap:
http://i.imgur.com/x4DdYaV.png
(PID 4899 just has a “where username = $1 cut off that you can’t see out to the right)
All updates are done using the username (unique constraint) instead of the primary key (the serial)
In retrospect, I suppose these queries should be using the primary key (9 year old code) but I am flummoxed as to how these updates can be causing table? level locks.
I’ve never been able to catch the lock information during one of these storms - but I assume it is a table level lock causing this.
Thoughts?  Is this just ‘normal’ behavior that I am not expecting? (because postgres doesn’t know that the username is a unique field)

And the login process is what, exactly, from the db perspective?

Re: table lock when where clause uses unique constraing instead of primary key.

From

Jeff Amiel

Date:

04 November 2013, 21:38:43


to: Rob Sargent

The login references have nothing to do with postgres - is simply table/column names being used.

Re: table lock when where clause uses unique constraing instead of primary key.

From

Rob Sargent

Date:

04 November 2013, 21:51:46

On 11/04/2013 11:38 AM, Jeff Amiel wrote:


to: Rob Sargent

The login references have nothing to do with postgres - is simply table/column names being used.

I'm sorry, I thought user_profile.login_attempts was being set to zero during login (or perhaps after successful login in a two-step process) and that interaction was leading to the "lock storm". Suspected something in the details there would be interesting.

Re: table lock when where clause uses unique constraing instead of primary key.

From

Adrian Klaver

Date:

04 November 2013, 22:48:10

On 11/04/2013 09:06 AM, Jeff Amiel wrote:
> PostgreSQL 9.2.4 on x86_64-pc-solaris2.10, compiled by gcc (GCC) 3.4.3 (csl-sol210-3_4-branch+sol_rpath), 64-bit
> Have got an annoying scenario that has been creating issues for us for years….
> Time to try to figure it out.
> Essentially, we have a user table where we maintain username, id number, enabled/disabled state, etc.
> When a user logs in successfully, we reset any failed login attempts on the user’s unique entry in this table.
>
> CREATE TABLE user_profile
> (
>    user_id serial NOT NULL,
>    username character varying(50) NOT NULL,
>    login_attempts integer DEFAULT 0,
>    …
>    CONSTRAINT user_id PRIMARY KEY (user_id),
>    CONSTRAINT name UNIQUE (username)
> )
>
> However - we often get “lock storms” where SOMEHOW, updates for individual users are causing all other updates to
‘lock’on each other. 
> Eventually the storm abates (sometimes in seconds - sometimes in minutes)
> See edited screen cap:
> http://i.imgur.com/x4DdYaV.png
> (PID 4899 just has a “where username = $1 cut off that you can’t see out to the right)
> All updates are done using the username (unique constraint) instead of the primary key (the serial)
> In retrospect, I suppose these queries should be using the primary key (9 year old code) but I am flummoxed as to how
theseupdates can be causing table? level locks. 
> I’ve never been able to catch the lock information during one of these storms - but I assume it is a table level lock
causingthis. 
> Thoughts?  Is this just ‘normal’ behavior that I am not expecting? (because postgres doesn’t know that the username
isa unique field) 

Any triggers on user_profile?

Any FK relationship in either direction?


>
>


--
Adrian Klaver
adrian.klaver@gmail.com

Re: table lock when where clause uses unique constraing instead of primary key.

From

Jeff Amiel

Date:

04 November 2013, 23:15:49

On Monday, November 4, 2013 1:48 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote:

>Any triggers on user_profile?
>Any FK relationship in either direction?

I grepped the schema (just to be sure) - no foreign keys on columns or table at all.
I do have an audit trigger on the table for updates - inserts into an audit table when changes are made and it DOES do
aseparate select from user_profile for other reasons - but not "for update" or anything - no explicit locking.

Re: table lock when where clause uses unique constraing instead of primary key.

From

Adrian Klaver

Date:

04 November 2013, 23:25:55

On 11/04/2013 12:15 PM, Jeff Amiel wrote:
>
>
>
>
>
> On Monday, November 4, 2013 1:48 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
>
>
>> Any triggers on user_profile?
>> Any FK relationship in either direction?
>
>
> I grepped the schema (just to be sure) - no foreign keys on columns or table at all.
> I do have an audit trigger on the table for updates - inserts into an audit table when changes are made and it DOES
doa separate select from user_profile for other reasons - but not "for update" or anything - no explicit locking. 
>

Would it be possible to see that audit function?

--
Adrian Klaver
adrian.klaver@gmail.com

Re: table lock when where clause uses unique constraing instead of primary key.

From

Jeff Amiel

Date:

04 November 2013, 23:44:13





On Monday, November 4, 2013 2:25 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote:

>
> I grepped the schema (just to be sure) - no foreign keys on columns or table at all.
> I do have an audit trigger on the table for updates - inserts into an audit table when changes are made and it DOES
doa separate select from user_profile for other reasons - but not "for update" or anything - no explicit locking. 
>

>Would it be possible to see that audit function?

it's kind of long (really just a lot of compares of old/new values.
The relevant portion (that selects from user_profile) looks like this:

BEGIN
            SELECT user_id, user_ip INTO my_user_id, my_user_ip FROM audit_metadata WHERE pg_pid = getpid();
            IF ((NOT FOUND) OR (my_user_id = -1)) THEN
                SELECT user_id INTO my_user_id FROM user_profile WHERE username = 'db-'||CURRENT_USER and
user_type='DBASE';
                IF (NOT FOUND) THEN
                     RAISE EXCEPTION 'USERNAME NOT FOUND IN USER_PROFILE: % ',CURRENT_USER;
                END IF;
                my_user_ip := inet_client_addr();
            END IF;

            INSERT INTO audit .....
       
        END;

Re: table lock when where clause uses unique constraing instead of primary key.

From

Adrian Klaver

Date:

04 November 2013, 23:56:58

On 11/04/2013 12:44 PM, Jeff Amiel wrote:
>
>
>
>
>
> On Monday, November 4, 2013 2:25 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
>
>>
>> I grepped the schema (just to be sure) - no foreign keys on columns or table at all.
>> I do have an audit trigger on the table for updates - inserts into an audit table when changes are made and it DOES
doa separate select from user_profile for other reasons - but not "for update" or anything - no explicit locking. 
>>
>
>> Would it be possible to see that audit function?
>
> it's kind of long (really just a lot of compares of old/new values.
> The relevant portion (that selects from user_profile) looks like this:
>
> BEGIN
>              SELECT user_id, user_ip INTO my_user_id, my_user_ip FROM audit_metadata WHERE pg_pid = getpid();
>              IF ((NOT FOUND) OR (my_user_id = -1)) THEN
>                  SELECT user_id INTO my_user_id FROM user_profile WHERE username = 'db-'||CURRENT_USER and
user_type='DBASE';
>                  IF (NOT FOUND) THEN
>                       RAISE EXCEPTION 'USERNAME NOT FOUND IN USER_PROFILE: % ',CURRENT_USER;
>                  END IF;
>                  my_user_ip := inet_client_addr();
>              END IF;
>
>              INSERT INTO audit .....
>
>          END;
>

Hmmm, nothing obvious here.

In the screenshot you posted what are the columns indicating, in
particular the third one?

Assuming the third column is pointing to the pid of the offending query
it is interesting that the other queries are coming from other IPs.
Almost as if the original query is bouncing off something. Is that possible?

--
Adrian Klaver
adrian.klaver@gmail.com

Re: table lock when where clause uses unique constraing instead of primary key.

From

Rob Sargent

Date:

05 November 2013, 00:10:37

On 11/04/2013 01:56 PM, Adrian Klaver wrote:

On 11/04/2013 12:44 PM, Jeff Amiel wrote:

On Monday, November 4, 2013 2:25 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote:

I grepped the schema (just to be sure) - no foreign keys on columns or table at all.
I do have an audit trigger on the table for updates - inserts into an audit table when changes are made and it DOES do a separate select from user_profile for other reasons - but not "for update" or anything - no explicit locking.

Would it be possible to see that audit function?

it's kind of long (really just a lot of compares of old/new values.
The relevant portion (that selects from user_profile) looks like this:

BEGIN
             SELECT user_id, user_ip INTO my_user_id, my_user_ip FROM audit_metadata WHERE pg_pid = getpid();
             IF ((NOT FOUND) OR (my_user_id = -1)) THEN
                 SELECT user_id INTO my_user_id FROM user_profile WHERE username = 'db-'||CURRENT_USER and user_type='DBASE';
                 IF (NOT FOUND) THEN
                      RAISE EXCEPTION 'USERNAME NOT FOUND IN USER_PROFILE: % ',CURRENT_USER;
                 END IF;
                 my_user_ip := inet_client_addr();
             END IF;

             INSERT INTO audit .....

         END;

Hmmm, nothing obvious here.

In the screenshot you posted what are the columns indicating, in particular the third one?

Assuming the third column is pointing to the pid of the offending query it is interesting that the other queries are coming from other IPs. Almost as if the original query is bouncing off something. Is that possible?

Are we sure the interaction with audit_metadata is clean and tidy?

Re: table lock when where clause uses unique constraing instead of primary key.

From

Adrian Klaver

Date:

05 November 2013, 00:23:57

On 11/04/2013 01:16 PM, Jeff Amiel wrote:
>
>
>
>
>
> On Monday, November 4, 2013 2:56 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
>
>> In the screenshot you posted what are the columns indicating, in
>> particular the third one?
>
>> Assuming the third column is pointing to the pid of the offending query
>> it is interesting that the other queries are coming from other IPs.
>> Almost as if the original query is bouncing off something. Is that possible?
>
> The third column is indeed the pid of the backend query that the query is 'blocked' by.
> Hmm...what means "bouncing off"?
>

Probably poor choice of words:). So then, what we are looking at is
other clients trying to update user_profile but not succeeding because
pid 4899 is blocking. At this point all I can see is that the offending
query is updating some fields the others are not; disabled and reset_code.

Is that always the case?

If so any thing in the code path that is different when those fields are
updated?

--
Adrian Klaver
adrian.klaver@gmail.com

Re: table lock when where clause uses unique constraing instead of primary key.

From

Jeff Amiel

Date:

05 November 2013, 00:56:21

On Monday, November 4, 2013 3:23 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote:

>Probably poor choice of words:). So then, what we are looking at is
>other clients trying to update user_profile but not succeeding because
>pid 4899 is blocking. At this point all I can see is that the offending
>query is updating some fields the others are not; disabled and reset_code.
>
>Is that always the case?
>
>If so any thing in the code path that is different when those fields are
>updated?

We have scenarios where exact same query is in play in all instances.
Any thoughts as to the fact that this could be a full table_lock simply based on the use of username (non primary key -
butspecifically unique constraint) in the where clause?  I'm grasping I know....

Re: table lock when where clause uses unique constraing instead of primary key.

From

Adrian Klaver

Date:

05 November 2013, 01:51:35

On 11/04/2013 01:56 PM, Jeff Amiel wrote:
>
>
>
>
>
> On Monday, November 4, 2013 3:23 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
>
>
>> Probably poor choice of words:). So then, what we are looking at is
>> other clients trying to update user_profile but not succeeding because
>> pid 4899 is blocking. At this point all I can see is that the offending
>> query is updating some fields the others are not; disabled and reset_code.
>>
>> Is that always the case?
>>
>> If so any thing in the code path that is different when those fields are
>> updated?
>
> We have scenarios where exact same query is in play in all instances.

Which query is that?
And what scenario are you talking about, blocking query or something else?

> Any thoughts as to the fact that this could be a full table_lock simply based on the use of username (non primary key
-but specifically unique constraint) in the where clause?  I'm grasping I know.... 

What makes you think the username condition is the problem?

>
>


--
Adrian Klaver
adrian.klaver@gmail.com