5

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?

  • 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
3

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.

  • 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
  • 1
    can a 3rd party insert something in the Table A during my transaction? – svirk May 29 '12 at 18:46
  • 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
  • 1
    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
  • 1
    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
0

I have done a test about locking. I started two connections which called A and B to execute commands. The operation flow list as below.

  1. A: set autocommit=0;
  2. A: update sometable set status = 0 where columns_a in(234,333) and is_del=0;
  3. B: set autocommit=0;
  4. B: update sometable set status = 0 where columns_a in(222,444) and is_del=0;
  5. A: commit;
  6. B: commit;

scenarios A: If columns_a is set as primary key of the sometable, step 4 executed and finished immediately.

scenarios B: If columns_a isn't the primary key, but we had add an index on column_a of sometable, step 4 executed and finished immediately.

scenarios C: If columns_a is set as primary key of the sometable, step 4 executed and finished in no time.

scenarios D: If columns_a isn't the primary key, step 4 would be waiting for the lock until step 5 executed.

I wonder if the 'lock' is table level lock which be attempt to acquire by step 4 in scenarios D .

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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