Re: How are locks managed in PG?

Thomas Kellerer wrote:

> 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 :) )

We use an in-memory lock manager for table- and page-level locks. For
shared tuple locks, they are spilled to disk on an ad-hoc storage system
(pg_multixact) when there is more than one shared locker. (Exclusive
locks and single locker shared locks are stored directly on the locked
tuple.)

> 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.

None of the above. We're smarter than everyone else.

> 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.

Table and page. (Actually tuple locks go through it too, but these
locks are short-lived; the transaction-long locks are stored elsewhere
as explained above.)

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support