Hi,
I've googled but couldn't find solution.
on postgresql logs. I see those messages.
< db:modna time=2011-11-02 10:35:44.184 EET>LOG: process 22254 acquired ShareLock on transaction 45479426 after 308021.895 ms
< db:modna time=2011-11-02 10:35:44.184 EET>ERROR: duplicate key value violates unique constraint "auth_user_username_key"
< db:modna time=2011-11-02 10:35:44.184 EET>LOG: process 22301 acquired ShareLock on transaction 45479426 after 78455.219 ms
< db:modna time=2011-11-02 10:35:44.184 EET>ERROR: duplicate key value violates unique constraint "auth_user_username_key"
all of these statements are simple insert queries. and they are same queries. `duplicate key value violates ` before this query
postgresql runs the same insert query after that these errors are occuring.
modna=# select * from pg_stat_activity where procpid = 22254;
-[ RECORD 1 ]-+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
datid | 16404
datname | modna
procpid | 22254
usesysid | 16403
usename | modnauser
current_query | INSERT INTO "auth_user" ("username", "first_name", "last_name", "email", "password", "is_staff", "is_active", "is_superuser", "last_login", "date_joined") VALUES (**********)
waiting | t
xact_start | 2011-11-02 10:38:55.112845+02
query_start | 2011-11-02 10:38:55.312484+02
backend_start | 2011-11-02 10:09:41.136666+02
client_addr | 192.168.3.85
client_port | 47414
----- PG_LOCKS OUTPUT -----------
modna=# select * from pg_locks where pid = 22254;
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted
---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+------------------+---------
relation | 16404 | 17053 | | | | | | | | 11/37536 | 22254 | AccessShareLock | t
transactionid | | | | | | 45481770 | | | | 11/37536 | 22254 | ExclusiveLock | t
relation | 16404 | 16433 | | | | | | | | 11/37536 | 22254 | AccessShareLock | t
relation | 16404 | 16433 | | | | | | | | 11/37536 | 22254 | RowExclusiveLock | t
relation | 16404 | 17046 | | | | | | | | 11/37536 | 22254 | AccessShareLock | t
relation | 16404 | 17714 | | | | | | | | 11/37536 | 22254 | AccessShareLock | t
relation | 16404 | 3808220 | | | | | | | | 11/37536 | 22254 | AccessShareLock | t
relation | 16404 | 3808220 | | | | | | | | 11/37536 | 22254 | RowExclusiveLock | t
transactionid | | | | | | 45481698 | | | | 11/37536 | 22254 | ShareLock | f
relation | 16404 | 3654638 | | | | | | | | 11/37536 | 22254 | AccessShareLock | t
relation | 16404 | 3654638 | | | | | | | | 11/37536 | 22254 | RowExclusiveLock | t
relation | 16404 | 3653834 | | | | | | | | 11/37536 | 22254 | AccessShareLock | t
relation | 16404 | 3653834 | | | | | | | | 11/37536 | 22254 | RowExclusiveLock | t
relation | 16404 | 16427 | | | | | | | | 11/37536 | 22254 | AccessShareLock | t
relation | 16404 | 16427 | | | | | | | | 11/37536 | 22254 | RowExclusiveLock | t
relation | 16404 | 16425 | | | | | | | | 11/37536 | 22254 | AccessShareLock | t
virtualxid | | | | | 11/37536 | | | | | 11/37536 | 22254 | ExclusiveLock | t
relation | 16404 | 16431 | | | | | | | | 11/37536 | 22254 | AccessShareLock | t
relation | 16404 | 16431 | | | | | | | | 11/37536 | 22254 | RowExclusiveLock | t
(19 rows)
Why those locks are occuring? I saw 2500+ locks yesterday. What can be done?
we use psycopg2.14 and pgbouncer.
