Thread: SELECT FOR SHARE and FOR UPDATE
Hello! I want to select data from two tables obtaining exclusive lock for records of the first table and nonexclusive lock for records of the second one. In other words, I need something like select a.f, b.f from a,b for update of a for share of b. Any hints? Thanks. -- Best regards Ilja Golshtein
On Fri, Oct 07, 2005 at 01:18:03PM +0400, Ilja Golshtein wrote: > Hello! > > I want to select data from two tables obtaining > exclusive lock for records of the first table and > nonexclusive lock for records of the second one. > > In other words, I need something like > select a.f, b.f from a,b for update of a for share of b. > Any hints? > > Thanks. From http://www.postgresql.org/docs/8.0/interactive/sql-select.html: FOR UPDATE [ OF table_name [, ...] ] I'm assuming that the syntax is the same for FOR SHARE. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
On Fri, Oct 07, 2005 at 09:18:00PM -0500, Jim C. Nasby wrote: > On Fri, Oct 07, 2005 at 01:18:03PM +0400, Ilja Golshtein wrote: > > I want to select data from two tables obtaining > > exclusive lock for records of the first table and > > nonexclusive lock for records of the second one. > > > > In other words, I need something like > > select a.f, b.f from a,b for update of a for share of b. > > From http://www.postgresql.org/docs/8.0/interactive/sql-select.html: > FOR UPDATE [ OF table_name [, ...] ] > > I'm assuming that the syntax is the same for FOR SHARE. It sounds like Ilja wants to do both FOR UPDATE and FOR SHARE in the same SELECT statement. According to the 8.1 documentation that's not allowed: http://developer.postgresql.org/docs/postgres/sql-select.html#SQL-FOR-UPDATE-SHARE "It is currently not allowed for a single SELECT statement to include both FOR UPDATE and FOR SHARE, nor can different parts of the statement use both NOWAIT and normal waiting mode." -- Michael Fuhr
[Please copy the mailing list on replies so others can participate in and learn from the discussion.] On Sat, Oct 08, 2005 at 11:16:08AM +0400, Ilja Golshtein wrote: > I started to believe SELECT ... FOR SHARE is the remedy for my > problems. Unfortunately it is not till I cannot combine share and > exclusive locks for different tables in one query. > > I wonder if this limitation is fundamental or such a mixing of > lock modes could be allowed in future releases? I badly need this > feature. The documentation says "It is currently not allowed," which suggests that perhaps it could be allowed in a future version. Don't expect to see it in 8.1, however, since that version is long past feature freeze. I don't recall how much, if any, discussion there was on this; search the pgsql-hackers archives to find out. Could you tell us more about what you're doing? Maybe there's another way to achieve it. -- Michael Fuhr