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

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?

to: Rob Sargent

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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 successfulloginin a two-step process) and
that interaction was leading to the "lock storm". Suspected something in
the details therewould be interesting.

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 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)

Any triggers on user_profile?

Any FK relationship in either direction?

--
Adrian Klaver
adrian.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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 a separate select from user_profile for other reasons - but not "for update" or anything - no explicit locking.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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 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?

--
Adrian Klaver
adrian.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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;

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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?

--
Adrian Klaver
adrian.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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?

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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 - but specifically unique constraint) in the where clause?  I'm grasping I know....

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general