How are locks managed in PG?

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.

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:

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

Alvaro Herrera, 19.12.2008 13:49:

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.

Thanks. This was the answer I was looking for :)

Thomas

On Fri, Dec 19, 2008 at 7:49 AM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:

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.

Which is why Oracle's locks are more scalable than PG's?

--
Jonah H. Harris, Senior DBA
myYearbook.com

On Sun, Dec 21, 2008 at 08:46:15PM -0500, Jonah H. Harris wrote:

On Fri, Dec 19, 2008 at 7:49 AM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:

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.

Which is why Oracle's locks are more scalable than PG's?

You've been talking about your super-secret test which you allege,
quite implausibly, I might add, to have Oracle (8i, even!) blowing
PostgreSQL's doors off for weeks now.

Put up, or shut up.

Regards,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On Sun, Dec 21, 2008 at 9:42 PM, David Fetter <david@fetter.org> wrote:

On Sun, Dec 21, 2008 at 08:46:15PM -0500, Jonah H. Harris wrote:

On Fri, Dec 19, 2008 at 7:49 AM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:

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.

Which is why Oracle's locks are more scalable than PG's?

You've been talking about your super-secret test which you allege,
quite implausibly, I might add, to have Oracle (8i, even!) blowing
PostgreSQL's doors off for weeks now.

Put up, or shut up.

Same to the standard PG B.S. responses such as, "None of the above.
We're smarter than everyone else." When's the last time Alvaro used
or tuned Oracle? Does he have a clue about how Oracle locks scale?
Stop complaining.

--
Jonah H. Harris, Senior DBA
myYearbook.com

On Sun, Dec 21, 2008 at 8:48 PM, Jonah H. Harris <jonah.harris@gmail.com> wrote:

On Sun, Dec 21, 2008 at 9:42 PM, David Fetter <david@fetter.org> wrote:

On Sun, Dec 21, 2008 at 08:46:15PM -0500, Jonah H. Harris wrote:

On Fri, Dec 19, 2008 at 7:49 AM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:

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.

Which is why Oracle's locks are more scalable than PG's?

You've been talking about your super-secret test which you allege,
quite implausibly, I might add, to have Oracle (8i, even!) blowing
PostgreSQL's doors off for weeks now.

Put up, or shut up.

Same to the standard PG B.S. responses such as, "None of the above.
We're smarter than everyone else." When's the last time Alvaro used
or tuned Oracle? Does he have a clue about how Oracle locks scale?
Stop complaining.

The difference is HE put forth an opinion about the pg developers
being smarter, but you put forth what seems like a statement of fact
with no evidence to back it up. One is quite subjective and open for
debate on both sides, and often to good effect. The other is a
statement of fact regarding scalability in apparently all usage
circumstances, since it wasn't in any way clarified if you were
talking about a narrow usage case or all of the possible and / or
probably ones.

Having dealt with cust service for a few commercial dbs, I can safely
say I get way better service from way smarter people when I have a
problem. And I don't have a lot of problems.

Having dealt with cust service for a few commercial dbs, I can safely
say I get way better service from way smarter people when I have a
problem. And I don't have a lot of problems.

Clarificiation: That's saying I get better service and such from pg
users / developers than anywhere else.

On Sun, Dec 21, 2008 at 11:04 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:

Having dealt with cust service for a few commercial dbs, I can safely
say I get way better service from way smarter people when I have a
problem. And I don't have a lot of problems.

Clarificiation: That's saying I get better service and such from pg
users / developers than anywhere else.

I'd agree with that. Unless you have lots of $$$ and/or know someone
at the commercial companies, it takes a lot of work to get a hold of
someone knowledgeable.

--
Jonah H. Harris, Senior DBA
myYearbook.com

On Sun, Dec 21, 2008 at 11:02 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:

The difference is HE put forth an opinion about the pg developers
being smarter, but you put forth what seems like a statement of fact
with no evidence to back it up. One is quite subjective and open for
debate on both sides, and often to good effect. The other is a
statement of fact regarding scalability in apparently all usage
circumstances, since it wasn't in any way clarified if you were
talking about a narrow usage case or all of the possible and / or
probably ones.

Agreed. It's just that, because I know quite a few of the engineers
working on Oracle and SQL Server, it generally pisses me off to see
people make blanket statements about one group being smarter than
another when they probably have no basis for comparison. It's all
good though, I'm just cranky tonight.

-Jonah

Jonah H. Harris wrote:

On Sun, Dec 21, 2008 at 11:02 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:

The difference is HE put forth an opinion about the pg developers
being smarter, but you put forth what seems like a statement of fact
with no evidence to back it up. One is quite subjective and open for
debate on both sides, and often to good effect. The other is a
statement of fact regarding scalability in apparently all usage
circumstances, since it wasn't in any way clarified if you were
talking about a narrow usage case or all of the possible and / or
probably ones.

Agreed. It's just that, because I know quite a few of the engineers
working on Oracle and SQL Server, it generally pisses me off to see
people make blanket statements about one group being smarter than
another when they probably have no basis for comparison. It's all
good though, I'm just cranky tonight.

I am sure there are smart people at all the database companies. I do
believe that open source development harnesses the abilities of its
intelligent people better than commercial companies.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

On Sun, Dec 21, 2008 at 9:35 PM, Bruce Momjian <bruce@momjian.us> wrote:

Jonah H. Harris wrote:

On Sun, Dec 21, 2008 at 11:02 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:

The difference is HE put forth an opinion about the pg developers
being smarter, but you put forth what seems like a statement of fact
with no evidence to back it up. One is quite subjective and open for
debate on both sides, and often to good effect. The other is a
statement of fact regarding scalability in apparently all usage
circumstances, since it wasn't in any way clarified if you were
talking about a narrow usage case or all of the possible and / or
probably ones.

Agreed. It's just that, because I know quite a few of the engineers
working on Oracle and SQL Server, it generally pisses me off to see
people make blanket statements about one group being smarter than
another when they probably have no basis for comparison. It's all
good though, I'm just cranky tonight.

I am sure there are smart people at all the database companies. I do
believe that open source development harnesses the abilities of its
intelligent people better than commercial companies.

I think one of the points that proves this is the chunks of innovative
code that have been put into postgresql that were basically written by
one or two guys in < 1 year. Small sharp teams can tackle one
particular problem and do it very well in an open source project.

On Mon, Dec 22, 2008 at 5:41 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:

I think one of the points that proves this is the chunks of innovative
code that have been put into postgresql that were basically written by
one or two guys in < 1 year. Small sharp teams can tackle one
particular problem and do it very well in an open source project.

Which is precisely why big smart companies divide up projects into
smaller teams - to achieve same goal. it is well known fact, that more
developers means more chaos, and less done on time. As my friend puts
it - you cannot expect 9 pregnant woman to deliver in 1 month :) I
know for a fact that microsoft, xensource and few others tackle
projects in small teams of brilliant engineers.
I don't know how oracle does it, but the whole thing is rather hudge,
so there must be quite few developers involved - at least in whole
middleware. Installing it on my laptop took about 2 hours (MBP, 2GB of
ram, centos) - compared to postgresql... Thank god pg developers not
decided to use java gui to 'script' whole thing, I think oracle would
be much better off without whole java crap around it (but that's just
my opinion).

--
GJ

Jonah H. Harris wrote:

On Sun, Dec 21, 2008 at 11:02 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:

The difference is HE put forth an opinion about the pg developers
being smarter, but you put forth what seems like a statement of fact
with no evidence to back it up. One is quite subjective and open for
debate on both sides, and often to good effect. The other is a
statement of fact regarding scalability in apparently all usage
circumstances, since it wasn't in any way clarified if you were
talking about a narrow usage case or all of the possible and / or
probably ones.

Agreed. It's just that, because I know quite a few of the engineers
working on Oracle and SQL Server, it generally pisses me off to see
people make blanket statements about one group being smarter than
another when they probably have no basis for comparison. It's all
good though, I'm just cranky tonight.

I still haven't seen a post regarding the Oracle scalability issue.
Where is the data??

--
Until later, Geoffrey

Those who would give up essential Liberty, to purchase a little
temporary Safety, deserve neither Liberty nor Safety.
- Benjamin Franklin

On Mon, Dec 22, 2008 at 7:37 AM, Geoffrey <lists@serioustechnology.com> wrote:

I still haven't seen a post regarding the Oracle scalability issue. Where is
the data??

You mean the PG scalability issue in comparison to Oracle?

--
Jonah H. Harris, Senior DBA
myYearbook.com

Scott Marlowe escribi�:

The difference is HE put forth an opinion about the pg developers
being smarter, but you put forth what seems like a statement of fact
with no evidence to back it up.

The other difference is that I said it jokingly, whereas you (Jonah)
seem to be bitter about the whole matter.

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

On Mon, Dec 22, 2008 at 8:22 AM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:

The other difference is that I said it jokingly, whereas you (Jonah)
seem to be bitter about the whole matter.

Well, it wasn't clear and I was just in a generally bad mood. Usually
you'd add a :) at the end, which you didn't this time. So, I wasn't
sure whether you were being serious or not.

I'm only bitter about people bashing things they don't know just for
the sake of bashing them. It wasn't anything directly against you,
it's just that the anti-any-other-database types of comments seem to
perpetuate more misunderstanding of the other systems. For the
record, the rest of your post was full of information, so I know
that's not what you were doing. It was just the aforementioned
comment, which I wasn't sure was a joke. That's why my response to
you was written as a question rather than a lengthy discussion of
how/why Oracle does things that way.

-Jonah

In-Reply-to: <200812220435.mBM4Zmd07588@momjian.us>

On: Sun, 21 Dec 2008 23:35:48 -0500 (EST), Bruce Momjian
<bruce@momjian.us> wrote:

I am sure there are smart people at all the database companies. I do
believe that open source development harnesses the abilities of its
intelligent people better than commercial companies.

I doubt that this is the case. In fact, I would venture that the chief
advantage of open source / free software projects over their commercial
brethren is that there is no "harness" at all.

The advantage of OS/FS is that theirs is truly a Darwinian struggle. OS
Projects that prove less fit for their environment pass away fairly
quickly for want of any real support. This is especially evident when a
significantly superior approach evolves outside the project or the social
behaviour of the team leads to self destructive actions within.

Commercial projects are not as directly susceptible to this process as
they do not exist for their own sake, but rather as an artifact of another
process, that of a commercial enterprise. Those projects survival is more
a consequence of, and dependent upon, the survival of the their supporting
social structure, the enterprise itself.

I think that to describe either OS or commercial software as better or
worse is misleading. The most that can be said is that each approach
serves a different purpose and exists in a different environment.

--
*** E-Mail is NOT a SECURE channel ***
James B. Byrne mailto:ByrneJB@Harte-Lyne.ca
Harte & Lyne Limited http://www.harte-lyne.ca
9 Brockley Drive vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada L8E 3C3