Re: How are locks managed in PG?

Thanks for the answer.

I know the concept of MVCC (and the chapter in the manual) and how locks are applied in PG.

What I would like to know how a lock (if it is acquired e.g. by doing an update) is technically managed inside PG.

Basically there are two solutions: a lock manager that stores a map for each "item" locked and the corresponding lock. This solution doesn't scale well, because the "management overhead" is linear to the number of locks. This is one of the reasons why one should avoid locks in SQL Server as much as possible. A high number of locks can actually slow down the server, not because of concurrency issues, but simply cpu problems (actually one should not only avoid locks but SQL Server alltogether :) )

Oracle on the other hand stores the lock information directly in the data block that is locked, thus the number of locks does not affect system performance (in terms of managing them).

I couldn't find any description on which strategy PG applies. There is something like a lock manager in the sources, but I don't know if that is actually used for row or table locking.

Regards
Thomas

Allan Kamau, 19.12.2008 11:04:
> Postgres by default uses the MVCC (Multiversion Concurrency Control,
> MVCC) for concurrency control. This is a large topic and may require
> more explanation than a simple email response would easily provide.
> The well written PostgreSQL documentation has good explanation on this
> topic (http://www.postgresql.org/docs/8.3/interactive/mvcc.html).
> If you explicity require table and row level locking the above link
> will provide answers on these too.
>
> Allan.
>
> On Fri, Dec 19, 2008 at 10:32 AM, Thomas Kellerer <spam_eater(at)gmx(dot)net> wrote:
>> Hi,
>>
>> I have a question on how PG manages lock information.
>> Does this go through a central "lock manager" that manages the information
>> which row is locked by which transactioni. Or is the lock information stored
>> directly within the data blocks (so no central "data structure" would be
>> needed)
>>
>> Thanks
>> Thomas
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>