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

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