Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

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;

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.