Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxx


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




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux