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.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

The Isolation level is REPEATABLE_READ.

The logic is as under:

Transaction begins
Read data from Table A
If (Table A has Any Data) End Transaction and exit
If Table A has No Data, Proceed further
Delete a record in Table B
Transaction ends

Now, my question is about the following scenario:

  • The current execution reaches at point #4.
  • I someone inserts data (Note, the current execution is at point # 4) in the Table A that would have returned some result in #2.
  • Now, The current execution will execute #5. It will delete a record that should not be deleted.

Is there any implicit locking in transaction or do I need to lock Table A Explicitly so no one can insert any data in Table A before I commit changes?

share|improve this question
Note, if someone inserts data .... Here, I am talking about another transaction (let's say some 3rd party) inserting data in the Table A – svirk May 29 '12 at 18:39

In repeatable reads, there is always row-level locking imposed via the gen_clust_index (aka the Clustered Index). This is the beauty of Transactions. What is even more interesting is that InnoDB has four transaction isolation levels, not just one:

There are four values for tx_isolation you can set:

In your particular case, inserting data into TableA actually does not get written to disk. The necessary changes are recorded in three(3) distinct places:

  1. log buffer in memory
  2. in ibdata1
    • undo tablespace
    • rollback segments
    • double write buffer
  3. redo log info in either ib_logfile0 or ib_logfile1

The same applies with the delete in step 5.

Executing a rollback will undo the delete and then undo the inserts.

You must remember something very important: If you want to rollback multiple SQL commands, you must begin like this:

SET autocommit = 0;

Transaction begins
Read data from Table A
If (Table A has Any Data) End Transaction (via ROLLBACK) and exit
If Table A has No Data, Proceed further
Delete a record in Table B
Transaction ends


Give it a Try !!!

When everyone is using repeatable reads

  • your INSERTs are only seen by you
  • someone else's DELETEs are only seen by the other person

CAVEAT : Table level locking is never implicit for InnoDB. If you want to lock a table, you must issue, LOCK TABLE explicitly.

share|improve this answer
Thanks for your answer. The question was about another transaction inserting data and committing before the current transaction commits it. – svirk May 29 '12 at 18:40
can a 3rd party insert something in the Table A during my transaction? – svirk May 29 '12 at 18:46
This depends on the app. If the app does not set set up a transaction with START TRANSACTION...COMMIT/ROLLBACK;, then the app with be just another client connection doing exactly what you are doing: writing to a common table. If the app does not use set autocomnit = 0; then it goes back to what I stated before: In a repeatable read situation, yes data can be inserted, especially if autocommit=1 (which is the default). – RolandoMySQLDBA May 29 '12 at 18:58
Autocommit=0 is unnecessary in the context if an explicit transaction. It only is necessary when executing individual statements outside the context of a transaction to implicitly create one. – Aaron Brown May 30 '12 at 3:37
To be more clear, START TRANSACTION implicitly sets autocommit=0, so it is unnecessary to execute it explicitly. – Aaron Brown May 30 '12 at 3:56

Your Answer


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.