Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.10.1/8.10.1) with ESMTP id e684eLW62888 for ; Sat, 8 Jul 2000 00:40:21 -0400 (EDT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id AAA23722; Sat, 8 Jul 2000 00:40:18 -0400 (EDT) To: Forest Wilkinson cc: pgsql-sql@hub.org Subject: Re: How to get a self-conflicting row level lock? In-reply-to: References: Comments: In-reply-to Forest Wilkinson message dated "Fri, 07 Jul 2000 18:58:56 -0700" Date: Sat, 08 Jul 2000 00:40:17 -0400 Message-ID: <23719.963031217@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200007/90 Forest Wilkinson writes: > I'm worried about concurrent process synchronization. According to the > PostgreSQL docs on the LOCK command, SELECT ... FOR UPDATE acquires a "ROW > SHARE MODE" lock, which is not self-conflicting. That doc is only telling part of the story, I guess. ROW SHARE MODE lock on a *table* is not self-conflicting, and should not be, because you want two different transactions to be able to acquire FOR UPDATE locks on different rows of the table concurrently. But SELECT FOR UPDATE also acquires a *per-tuple* lock on each selected row, and that lock will prevent another transaction from acquiring a FOR UPDATE lock on the same row(s). Try it and see. The reason SELECT FOR UPDATE acquires a table-wide lock at all is just to prevent table-wide conflicts, such as someone else trying to do a VACUUM or DROP TABLE on that table. I suspect that it doesn't really need to get a different kind of table lock than an UPDATE gets, but haven't thought about the details. regards, tom lane