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.kla...@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
