A SELECT query inside a transaction, in itself, is not properly shielded from UPDATEs and DELETEs.
What you need to use the following:
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;
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.
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.