Received: from mail.rdc1.sdca.home.com (imail@ha1.rdc1.sdca.home.com [24.0.3.66]) by hub.org (8.10.1/8.10.1) with ESMTP id e681w3W01720 for ; Fri, 7 Jul 2000 21:58:03 -0400 (EDT) Received: from tajar ([24.1.6.224]) by mail.rdc1.sdca.home.com (InterMail vM.4.01.03.00 201-229-121) with SMTP id <20000708015801.KBOT11347.mail.rdc1.sdca.home.com@tajar> for ; Fri, 7 Jul 2000 18:58:01 -0700 From: Forest Wilkinson To: pgsql-sql@hub.org Subject: How to get a self-conflicting row level lock? Date: Fri, 07 Jul 2000 18:58:56 -0700 Message-ID: X-Mailer: Forte Agent 1.8/32.548 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200007/89 I have become maintainer of a program that uses PostgreSQL 6.5.2 for database functionality. It is littered with code blocks that do the following: 1. SELECT * FROM some_table WHERE foo = bar FOR UPDATE; 2. -- Choose a new value for some_field, which might or might not be based on its original value. 3. UPDATE some_table SET some_field = new_value WHERE foo = bar; 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. This tells me that when two processes execute the same code block concurrently, this can happen: 1. Process A selects the desired row for update. It now has a copy of the original values in that row. 2. Process B does the same. (This is allowed because ROW SHARE MODE locks do not conflict with each other.) It now has a copy of the original values in that row. 3. Process A chooses a new value for the desired field, based on the original value. 4. Process B does the same. 5. Process A updates the row with its new value, and exits. 6. Process B updates the row with its new value, overwriting the changes made by process A. Is it true that SELECT ... FOR UPDATE only acquires a ROW SHARE MODE lock, and that it isn't self-conflicting? How can I acquire a self-conflicting row level lock? What is the proper way to perform operations like those I'm describing? Thank you, Forest