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: | Raw Message | Whole Thread | 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... |