Putting a Select statement in a transaction

What is the difference between these 2 queries:

START TRANSACTION;
SELECT * FROM orders WHERE id=1;
UPDATE orders SET username='John' WHERE id=1;
COMMIT;

And without transaction:

SELECT * FROM orders WHERE id=1;
UPDATE orders SET username='John' WHERE id=1;

What is the effect of having a SELECT inside a transaction?

If DELETE FROM orders WHERE id=1 was called from another session right after the SELECT in both cases, when will it be processed?

A SELECT query inside a transaction, in itself, is not properly shielded from UPDATEs and DELETEs.

What you need to use the following:

• SELECT ... FOR UPDATE and SELECT ... LOCK IN SHARE MODE

• Disable autocommit by doing one of the following:

• start transaction; (Which you have in your first query)

• set autocommit = 0;

If you issue Delete From orders Where id=1, it will happen once the rows in the orders table have released their locks at the end of the transaction. You could experiment (on a Dev/Staging Server, please) with using READ UNCOMMITTED transaction isolation level to make the delete logically happen, but only on commit will it become visible and recorded permanently.

In the second transaction, basically all bets are off. If you run

select * From orders Where id=1;
UPDATE orders SET username="John" Where id=1;

running Delete From orders Where id=1 will commit immediately. Depending on the order MySQL executes these statements, will you see (or not see) the delete rows.

CAVEAT

MySQL 5.6 now has the following:

• START TRANSACTION READ WRITE;

• START TRANSACTION READ ONLY;

The READ WRITE and READ ONLY modifiers set the transaction access mode. They permit or prohibit changes to tables used in the transaction. The READ ONLY restriction prevents the transaction from modifying or locking both transactional and nontransactional tables that are visible to other transactions; the transaction can still modify or lock temporary tables. These modifiers are available as of MySQL 5.6.5.

StackExchange Database Administrators Q#46459, answer score: 9