In InnoDB, does a Transaction imply any implicit locking of a table?

Question

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?

Answer 1

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:

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;
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.