Thread: Update / Lock (and ShareLock) question
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.
Augustin Amann <augustin@waw.com> writes:
> During lock, we could see things like this in log file:
> Process 5556 waits for ShareLock on transaction 14910066; blocked by
> process 4940.
What that really means is that the first process is waiting for a row
lock that's held by the second one --- that is, it's trying to update a
row that the second transaction has updated and not yet committed.
> Why waiting update are not simply queued ?
Uh, that's exactly what's happening.
regards, tom lane
Tom Lane a écrit :
> Augustin Amann <augustin@waw.com> writes:
>
>> During lock, we could see things like this in log file:
>> Process 5556 waits for ShareLock on transaction 14910066; blocked by
>> process 4940.
>>
>
> What that really means is that the first process is waiting for a row
> lock that's held by the second one --- that is, it's trying to update a
> row that the second transaction has updated and not yet committed.
>
>
Ok. So it's normal ...
Thank you for your fast reply. Good to know that it's not a design
problem...
>> Why waiting update are not simply queued ?
>>
>
> Uh, that's exactly what's happening.
>
>
I understand. But a dead lock is for me, a situation that sould not
appear, event if the storage is slow ... I'm wrong ?
Here is the log:
2008-07-10 19:26:41 CEST UPDATE waiting pid=8028 db=xxxx_db
sess=48764626.1f5c
ERROR: deadlock detected
2008-07-10 19:26:41 CEST UPDATE waiting pid=8028 db=xxxx_db
sess=48764626.1f5c
DETAIL: Process 8028 waits for ShareLock on transaction 14836545;
blocked by pr
ocess 8124.
Process 8124 waits for ShareLock on transaction 14837154;
blocked by process 8028.
2008-07-10 19:26:41 CEST UPDATE waiting pid=8028 db=xxxx_db
sess=48764626.1f5c
STATEMENT: UPDATE yyyyy SET display=display+1 WHERE id='73' AND
hour='19' AND day='2008-07-10' ;
Look strange to me !
> regards, tom lane
>
>
Regards,
Augustin.
Augustin Amann <augustin@waw.com> writes:
> Tom Lane a écrit :
>> What that really means is that the first process is waiting for a row
>> lock that's held by the second one --- that is, it's trying to update a
>> row that the second transaction has updated and not yet committed.
> I understand. But a dead lock is for me, a situation that sould not
> appear, event if the storage is slow ... I'm wrong ?
If you're getting deadlocks on these, then what you have is two
concurrent transactions trying to update the same two tuples in
different orders. Which is a classic deadlock case, and the only
fix is to fix your app so that multiple updates are done in some
consistent order --- or broken into multiple transactions.
regards, tom lane
Tom Lane a écrit :
> Augustin Amann <augustin@waw.com> writes:
>
>> Tom Lane a écrit :
>>
>>> What that really means is that the first process is waiting for a row
>>> lock that's held by the second one --- that is, it's trying to update a
>>> row that the second transaction has updated and not yet committed.
>>>
>
>
>> I understand. But a dead lock is for me, a situation that sould not
>> appear, event if the storage is slow ... I'm wrong ?
>>
>
> If you're getting deadlocks on these, then what you have is two
> concurrent transactions trying to update the same two tuples in
> different orders. Which is a classic deadlock case, and the only
> fix is to fix your app so that multiple updates are done in some
> consistent order --- or broken into multiple transactions.
>
>
Yes, I understand, but my problem is that no transaction are involved in
our case, just one update same statement ...
I'll look for an hidden transaction (ask the dev, pgpool) :/ ...
Thank you for your confirming that.
Regards,
Augustin.
> regards, tom lane
>