Hello,
Is a important number of update (no transaction, just one statement
after another) could result in ShareLock ?
I read the doc, but it's not clear to me.
There is a website (written with php, and using pg_connect), on two
loadbalanced Webserver, and one PostgreSQL database, on another server.
All system is Debian Etch, PostgreSQL version is 8.1.11
We use PGPool 1 (version 3.1.1) between php and PostgreSQL, for
connection pooling only, not loadbalancing.
During website bench, we are using almost 100% of disk util (iostat said
:) !), so update take a long time (~ 1/2s), but it seems normal ...
The problem is sometimes, we got a ShareLock.
The only statement done by the php is an update with a filter like this:
2008-07-10 20:01:03 CEST UPDATE waiting pid=4940 db=xxxxx_db
sess=48764d2e.134c
STATEMENT: UPDATE yyyyyy SET display=display+1 WHERE id='73' AND
hour='19' AND day='2008-07-10' ;
During lock, we could see things like this in log file:
Process 5556 waits for ShareLock on transaction 14910066; blocked by
process 4940.
And in pg_locks:
xxxxx_db=# SELECT relation,mode, count(*) from pg_locks group by
relation,mode order by count(*);
relation | mode | count
----------+--------------------------------+-------
10342 | AccessShareLock | 1
| ShareLock | 2
17421 | RowExclusiveLock | 97
17421 | ExclusiveLock | 97
17421 | AccessShareLock | 97
| ExclusiveLock | 98
So the question is: Is it normal to get ShareLock with many same update
? RowExclusiveLock seems normal, but ExclusiveLock not. Why waiting
update are not simply queued ?
Is there a misconfiguration ? If I could change the lock strategy, could
I resolve this sharelock ?
I could ask developper to change application behaviour, and change
update to insert, but i prefer completly understand the problem before
asking for an application modification.
The next question is, if the performance on my poor scsci RAID1 slow
down update that it could result in sharelock, is there some tuning for
massive update (there is no index on the updated field) ?
Thank you in advance for giving me a better view on my problem, or some
hint to get rid of this problem ...
Best regards,
Augustin.
