In InnoDB, does a Transaction imply any implicit locking of a table? 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?
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:


READ-UNCOMMITTED
READ-COMMITTED
REPEATABLE-READ (default)
SERIALIZABLE


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:


log buffer in memory
in ibdata1


undo tablespace
rollback segments
double write buffer

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;
START TRANSACTION;

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

COMMIT;


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.