Postgresql deadlocks

[Martin Tiršel]

Zdravim,

mam nad Djangom a Postgresql postavenu aplikaciu a od vcera sa mi zacali  
(bez zmeny v aplikacii) objavovat deadlocky v PG. Doteraz to za niekolko  
mesiacov nenastalo, medzitym sa aplikacia prerabala a na novej verzii bezi  
niekolko tyzdnov. Doteraz nebol jediny problem s tymto. Od vcera kazdych  
niekolko minut, hoci aplikacia updatovana nebola, len pribudaju nove data  
v databaze.

Vie ma niekto nasmerovat, na co by som sa mal zamerat, pripadne skusit  
upravit? Logy vyzeraju nejako takto:

2012-03-01 14:52:27 CET ERROR:  deadlock detected
2012-03-01 14:52:27 CET DETAIL:  Process 10372 waits for ShareLock on  
transaction 130251607; blocked by process 10370.
	Process 10370 waits for ShareLock on transaction 130251610; blocked by  
process 10372.
	Process 10372: UPDATE "advert_advert" SET "views" =  
"advert_advert"."views" + 1 WHERE "advert_advert"."id" = 1152
	Process 10370: UPDATE "advert_advert" SET "views" =  
"advert_advert"."views" + 1 WHERE "advert_advert"."id" = 1153
2012-03-01 14:52:27 CET HINT:  See server log for query details.
2012-03-01 14:52:27 CET STATEMENT:  UPDATE "advert_advert" SET "views" =  
"advert_advert"."views" + 1 WHERE "advert_advert"."id" = 1152

Niekedy aj niekolko po sebe:

2012-03-01 14:56:14 CET ERROR:  deadlock detected
2012-03-01 14:56:14 CET DETAIL:  Process 12017 waits for ShareLock on  
transaction 130253008; blocked by process 12018.
	Process 12018 waits for ShareLock on transaction 130253007; blocked by  
process 12017.
	Process 12017: UPDATE "advert_advert" SET "views" =  
"advert_advert"."views" + 1 WHERE "advert_advert"."id" = 1165
	Process 12018: UPDATE "advert_advert" SET "views" =  
"advert_advert"."views" + 1 WHERE "advert_advert"."id" = 1151
2012-03-01 14:56:14 CET HINT:  See server log for query details.
2012-03-01 14:56:14 CET STATEMENT:  UPDATE "advert_advert" SET "views" =  
"advert_advert"."views" + 1 WHERE "advert_advert"."id" = 1165
2012-03-01 14:56:15 CET ERROR:  deadlock detected
2012-03-01 14:56:15 CET DETAIL:  Process 12023 waits for ShareLock on  
transaction 130253016; blocked by process 12026.
	Process 12026 waits for ShareLock on transaction 130253013; blocked by  
process 12023.
	Process 12023: UPDATE "advert_advert" SET "views" =  
"advert_advert"."views" + 1 WHERE "advert_advert"."id" = 1165
	Process 12026: UPDATE "advert_advert" SET "views" =  
"advert_advert"."views" + 1 WHERE "advert_advert"."id" = 1151
2012-03-01 14:56:15 CET HINT:  See server log for query details.
2012-03-01 14:56:15 CET STATEMENT:  UPDATE "advert_advert" SET "views" =  
"advert_advert"."views" + 1 WHERE "advert_advert"."id" = 1165
2012-03-01 14:56:16 CET ERROR:  deadlock detected
2012-03-01 14:56:16 CET DETAIL:  Process 12026 waits for ShareLock on  
transaction 130253018; blocked by process 12028.
	Process 12028 waits for ShareLock on transaction 130253016; blocked by  
process 12026.
	Process 12026: UPDATE "advert_advert" SET "views" =  
"advert_advert"."views" + 1 WHERE "advert_advert"."id" = 1151
	Process 12028: UPDATE "advert_advert" SET "views" =  
"advert_advert"."views" + 1 WHERE "advert_advert"."id" = 1165
2012-03-01 14:56:16 CET HINT:  See server log for query details.
2012-03-01 14:56:16 CET STATEMENT:  UPDATE "advert_advert" SET "views" =  
"advert_advert"."views" + 1 WHERE "advert_advert"."id" = 1151

Medzitym  su v logoch tieto zaznamy, ale to s tym zrejme nesuvisi:

2012-03-01 14:54:01 CET LOG:  could not receive data from client:  
Connection reset by peer
2012-03-01 14:54:01 CET LOG:  unexpected EOF on client connection
2012-03-01 14:54:02 CET LOG:  could not receive data from client:  
Connection reset by peer
2012-03-01 14:54:02 CET LOG:  unexpected EOF on client connection
2012-03-01 14:56:02 CET LOG:  could not receive data from client:  
Connection reset by peer
2012-03-01 14:56:02 CET LOG:  unexpected EOF on client connection
2012-03-01 14:56:02 CET LOG:  could not receive data from client:  
Connection reset by peer
2012-03-01 14:56:02 CET LOG:  unexpected EOF on client connection


Ak zadam tuto query (pripadne dalsie, ktore sa uvadzaju na  
http://wiki.postgresql.org/wiki/Lock_Monitoring pri rieseni problemo s  
uzamykanim):

select relation::regclass, * from pg_locks where not granted;

tak mi to ziadne zaznamy nevrati.

-- 
S pozdravom,
Martin Tiršel