basic SQL atomicity "UPDATE ... SET .. WHERE ..."

Question

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

having a table (without extra constraint),

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

now, I would execute following 4 statements "at the same time" (concurrently).

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 would be executed with table update? or, is it possible that more than one UPDATE statements can really update the table?

should I need extra transaction or lock to let only one UPDATE write values into table?

thanks

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

Answer 1

With the statement you posted and you would end up with an error because after the first update 'a' can't be found. What are you trying to achieve with this?

Answer 2

If you ran these UPDATEs in one go, it will run them in order, so it would update everything to b1 but then the other 3 would fail to update any as there will be no A's left to update.

Answer 3

There would be an implicit transaction around each which would hold other UPDATEs in a queue. Only the first one through would win in this case as each subsequent update will not see a name called 'a'.

Edit: I was assuming here that you were calling each UPDATE from separate processes. If they were called in a single script, they would be run consecutively in the order of appearence.

Answer 4

There is only ever one UPDATE statement that can access a record. Before it runs, it starts a transaction and locks the table (or more correctly, the page the record is on, or even only the record itself - this depends on many factors). Then it makes the change and unlocks the table again, commiting the transaction in the process.

Updates/deletes/inserts to a certain record are single threaded by their very nature, it is required to ensure table integrity. This does not mean that updates to many records (that are on different pages) could not run in parallel.

Answer 5

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

The others will wait for the resource to be freed.

When the lucky statement will commit, the others will either reread the table and do nothing (if your transaction isolation mode is set to READ COMMITTED) or fail to serialize the transaction (if the transaction isolation level is SERIALIZABLE).

Answer 6

Even if you don't specify begin transaction and commit transaction, a single SQL statement is always transactional. That means only one of the updates is allowed to modify the row at the same time. The other queries will block on the update lock owned by the first query.

Answer 7

I believe this may 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

There should be an equivalent function to row_number in the other SQL flavors.