Rural Hunter wrote:
> Kevin Grittner wrote:
>> Rural Hunter wrote:
>>
>>> I still have this question:
>>> same statement A,B,C,D update same row. The start order is
>>> A->B->C-D. From what I've gotten, B/C/D got the lock before A.
>>> Why did that happen?
>>
>> Did you do anything to prevent it from happening? If not, the OS
>> scheduler is going to give time to one process or another in a
>> fairly unpredictable way.
>
> hmm....no I didn't do anything. is the lock priority decided by OS
> not the DB?
What the heck is "lock priority"? I'm not familiar with that term.
> I'm confused here. B/C/D started several mins later than A here
> while the update statement takes no more than 1 second. of coz
> there are hundreds of connections trying to acquire the lock during
> that time.
Well, that sounds like your problem right there. If you have
hundreds of connections, among a great many other problems,
competition for locks can leave any one connection shut out for quite
a long time. If you have hundreds of connections, you will have this
and other problems indefinitely. Right now, look into setting up
pgpool or some other connection pooler to accept your hundreds of
connections, and funnel them into a number of connections somewhere
around double the number of cores on your server. Use transaction-
based pooling, so that when you have more transactions than the
server can efficiently handle, the extras will queue. This will
improve both your overall throughput and your latency (response
time).
Many people naively think that even when the server is saturated,
throwing another transaction at it right away will get a response for
it sooner. That is dead wrong.
-Kevin