Thread: How are locks managed in PG?

How are locks managed in PG?

From
Thomas Kellerer
Date:
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.
Oris the lock information stored directly within the data blocks (so no central "data structure" would be needed) 

Thanks
Thomas

Re: How are locks managed in PG?

From
"Allan Kamau"
Date:
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@gmx.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: How are locks managed in PG?

From
Thomas Kellerer
Date:
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.
Thissolution doesn't scale well, because the "management overhead" is linear to the number of locks. This is one of the
reasonswhy 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
Serveralltogether :) ) 

Oracle on the other hand stores the lock information directly in the data block that is locked, thus the number of
locksdoes 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,
butI 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@gmx.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@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>

Re: How are locks managed in PG?

From
Alvaro Herrera
Date:
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

Re: How are locks managed in PG?

From
Thomas Kellerer
Date:
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

Re: How are locks managed in PG?

From
"Jonah H. Harris"
Date:
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

Re: How are locks managed in PG?

From
David Fetter
Date:
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

Re: How are locks managed in PG?

From
"Jonah H. Harris"
Date:
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

Re: How are locks managed in PG?

From
"Scott Marlowe"
Date:
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.

Re: How are locks managed in PG?

From
"Scott Marlowe"
Date:
> 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.

Re: How are locks managed in PG?

From
"Jonah H. Harris"
Date:
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

Re: How are locks managed in PG?

From
"Jonah H. Harris"
Date:
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

Re: How are locks managed in PG?

From
Bruce Momjian
Date:
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. +

Re: How are locks managed in PG?

From
"Scott Marlowe"
Date:
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.

Re: How are locks managed in PG?

From
"Grzegorz Jaśkiewicz"
Date:
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

Re: How are locks managed in PG?

From
Geoffrey
Date:
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

Re: How are locks managed in PG?

From
"Jonah H. Harris"
Date:
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

Re: How are locks managed in PG?

From
Alvaro Herrera
Date:
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

Re: How are locks managed in PG?

From
"Jonah H. Harris"
Date:
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

Re: How are locks managed in PG?

From
"James B. Byrne"
Date:
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


Re: How are locks managed in PG?

From
"Jonah H. Harris"
Date:
On Mon, Dec 22, 2008 at 9:35 AM, James B. Byrne <byrnejb@harte-lyne.ca> wrote:
> 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.

Well said.

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

Re: How are locks managed in PG?

From
Geoffrey
Date:
Jonah H. Harris wrote:
> 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?

Yes.


--
Until later, Geoffrey

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

Re: How are locks managed in PG?

From
Christophe
Date:
Playing the straight man, I have to ask: Scalability issues with locks
in PG vs Oracle?

Re: How are locks managed in PG?

From
"Jonah H. Harris"
Date:
On Mon, Dec 22, 2008 at 12:34 PM, Christophe <xof@thebuild.com> wrote:
> Playing the straight man, I have to ask: Scalability issues with locks in PG
> vs Oracle?

(in slow motion) nooooooooo.  Locks aren't something particular I'd
like to discuss, this topic just came from a post upthread.

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