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?
