Have Geek Brain

Basic SQL Atomicity "UPDATE ... SET .. WHERE ..."

I have a rather simple and general question about the atomicity of the "UPDATE ... SET .. WHERE ..." statement.

having a table (no additional limitation),

+----------+
| id | name|
+----------+
|  1 |  a  |
+----+-----+

now I will execute the next 4 statements "at the same time" (simultaneously).

UPDATE table SET name='b1' WHERE name='a'
UPDATE table SET name='b2' WHERE name='a'
UPDATE table SET name='b3' WHERE name='a'
UPDATE table SET name='b4' WHERE name='a'

is there only one UPDATE statement to update the table? or, is it possible that more than one UPDATE statement can actually update the table?

Do I need an additional transaction or lock so that only one UPDATE value is written to the table?

thanks

[EDIT] 4 UPDATE statements are executed in parallel from different processes. [EDIT] with Postgresql

+2


a source to share


7 replies


One of these statements will lock the record (or the page or the entire table, depending on your mechanism and granularity) and will be executed.

The rest will wait for the resource to be released.



When the peer statement commits, the others will either re-read the table, or do nothing (if the transaction isolation mode is set to READ COMMITTED

), or failed to serialize the transaction (if the transaction isolation level is SERIALIZABLE

).

+8


a source


If you ran these UPDATEs in one go, they will run them in order, so it will update everything to b1 and then the other 3 will not be able to update them since there will be no A left to update.



+1


a source


There will be an implicit transaction around each one that will contain other UPDATEs in the queue. Only the first one would win in this case, since every subsequent update won't see the name named "a".

Edit: I assumed you were calling each UPDATE from separate processes. If they were called in the same script, they will run sequentially in the order they appear.

+1


a source


There is only one UPDATE statement that can access the record. Before starting it starts a transaction and locks the table (or rather, the page on which the record is written, or even the record itself - it depends on many factors). Then he makes the change and unlocks the table again, committing a transaction in the process.

Updates / deletes / inserts to a specific record are single-threaded by their very nature, it is necessary to ensure the integrity of the table. This does not mean that updates for many records (which are on different pages) cannot be done in parallel.

+1


a source


With the expression you posted and you will get an error because after the first update "a" could not be found. What are you trying to achieve with this?

0


a source


Even if you do not specify begin transaction

and commit transaction

, a single SQL statement is always transactional. This means that only one of the updates allows the row to be changed at a time. Other requests will be blocked on the update lock

first request.

0


a source


I believe this might be what you are looking for (in T-SQL):

UPDATE [table]
SET [table].[name] = temp.new_name
FROM
[table],
(
    SELECT
        id,
        new_name = 'B'+ cast(row_number() over (order by [name]) as varchar)
    FROM
        [table]
    WHERE
        [table].name = 'A'
) temp
WHERE [table].id = temp.id

Other SQL attributes must have an equivalent function row_number.

0


a source






More articles:


All Articles