Обсуждение: 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 >