Thread: process 22254 acquired ShareLock on transaction

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.