Обсуждение: How are locks managed in PG?
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
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 >
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 >> >
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
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
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
Playing the straight man, I have to ask: Scalability issues with locks in PG vs Oracle?
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