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

innodblockingMySQLtransaction

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?

Best 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:

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.

Related Solutions

Mysql – InnoDB row level locks without SELECTs

MySQL is taking too much of the innodb buffer pool size for your table locking.

Insert innodb_buffer_pool_size=2g in my.cnf file, which is most probably in /etc/mysql/

Postgresql – Read Committed Isolation Level

The difference lies between a query and a transaction. A transaction can contain any number of queries. To illustrate the difference, I set up a small example:

CREATE TABLE table_to_be_updated (
    id serial PRIMARY KEY,
    other_column text,
    column_changing text
);

INSERT INTO table_to_be_updated (other_column, column_changing)
VALUES
('value', 'old_value'),
('value', 'other_value'),
('nonvalue', 'doesnt matter');

Then run two transactions concurrently (issuing the commands one by one, the middle line wants to depict the timeline):

                                | <-- BEGIN;
                                |
                                |
                                |     UPDATE table_to_be_updated
BEGIN; -----------------------> |     SET column_changing = 'new_value'
                                |     WHERE
                                |         other_column = 'value' AND
                                |         column_changing = 'old_value';
                                |
                                |
SELECT column_changing -------> |     -- update not yet committed
FROM table_to_be_updated        |
WHERE other_column = 'value';   | <-- COMMIT;
                                |
                                |
SELECT column_changing -------> |
FROM table_to_be_updated        |
WHERE other_column = 'value';   |
                                |
                                |
COMMIT; ----------------------> |

Running these in READ COMMITTED isolation level, the first query returns a row with 'old_value', while the second one shows a row with 'new_value'. On an other run, I change the left-hand-side transaction isolation level:

SET transaction ISOLATION LEVEL REPEATABLE READ;

(The command must be the first statement in a transaction.) Now both SELECTs return the same rowset, while a third one after committing both transactions will show the new row.

Best Answer

Related Solutions

Mysql – InnoDB row level locks without SELECTs

Postgresql – Read Committed Isolation Level

Related Question