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
