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.

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 –  user395881 May 29 '12 at 18:39
add comment

1 Answer

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

share|improve this answer
    
Thanks for your answer. The question was about another transaction inserting data and committing before the current transaction commits it. –  user395881 May 29 '12 at 18:40
    
can a 3rd party insert something in the Table A during my transaction? –  user395881 May 29 '12 at 18:46
    
In a repeatable read situation, yes data can be inserted, especially if autocommit=1 (which is the default). –  RolandoMySQLDBA May 29 '12 at 18:47
    
Is there any other option available other than locking a table? And second thing is, I believe innodb has row level locking, so 3rd party won't be able to update the records that are read by my transaction. right? –  user395881 May 29 '12 at 18:49
1  
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
show 3 more comments

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.