Is an UPDATE transaction itself? Is an explicit transaction needed for a single atomic UPDATE?

The UPDATE operation in most modern DBMSs is atomic and executed within an internal transaction, even if no explicit transaction is set. This means that a single UPDATE itself is a transaction in terms of atomicity: either all changes are applied or none.

However, if you need to perform multiple operations (for example, several UPDATE, INSERT, DELETE) as a single unit to ensure data consistency, an explicit transaction (BEGIN TRANSACTION ... COMMIT/ROLLBACK) is required.

In summary:

• A single atomic UPDATE usually does not require an explicit transaction.
• To group multiple operations, an explicit transaction is needed.

Example in SQL:

-- One UPDATE without an explicit transaction
UPDATE users SET status = 'active' WHERE id = 123;

-- Multiple operations within an explicit transaction
BEGIN TRANSACTION;
UPDATE users SET status = 'active' WHERE id = 123;
INSERT INTO logs(user_id, action) VALUES (123, 'activated');
COMMIT;