| From: | Forest Wilkinson <fspam(at)home(dot)com> | 
|---|---|
| To: | pgsql-sql(at)hub(dot)org | 
| Subject: | How to get a self-conflicting row level lock? | 
| Date: | 2000-07-08 01:58:56 | 
| Message-ID: | ck2dms0t8ab52edhqbn5oetl4v3s2majsa@4ax.com | 
| Views: | Whole Thread | Raw Message | Download mbox | Resend email | 
| Thread: | |
| Lists: | pgsql-sql | 
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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Philip Warner | 2000-07-08 02:00:31 | Re: Re: [SQL] Re: [GENERAL] lztext and compression ratios... | 
| Previous Message | Bruce Momjian | 2000-07-08 01:54:54 | Re: Re: [SQL] Re: [GENERAL] lztext and compression ratios... |