How to get a self-conflicting row level lock?
От | Forest Wilkinson |
---|---|
Тема | How to get a self-conflicting row level lock? |
Дата | |
Msg-id | ck2dms0t8ab52edhqbn5oetl4v3s2majsa@4ax.com обсуждение исходный текст |
Ответы |
Re: How to get a self-conflicting row level lock?
|
Список | 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 hasa 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
В списке pgsql-sql по дате отправления: