Search code examples
mysqltransactionsinnodb

InnoDB transaction principle

Sorry if this is stupid question. I have MySQL InnoDB application with 70+ tables and using transactions. Everything is working fine except one thing (table):

CREATE TABLE IF NOT EXISTS `mag_pj_art_sums` (
`id` int(11) NOT NULL,  (primary key)
`id_pj` int(11) NOT NULL,   (index)
`id_artikal` int(11) NOT NULL,  (index)
`kol_stanje_knjig` decimal(18,2) DEFAULT NULL)

I’m using same principle for all queries:

START TRANSACTION (query('set autocommit=0;'); query('START TRANSACTION;');)
SELECT … FROM table WHERE …
UPDATE TABLE SET …. WHERE ….
COMIT

In ALL tables PRIMARY key is used for SELECT and UPDATE (in below query schema).

Except mag_pj_art_sums where I use:

SELECT … FROM mag_pj_art_sums WHERE (id_artikal='$id_artikal' AND id_pj='$id_pj')

and 

UPDATE mag_pj_art_sums SET … WHERE (id_artikal='$id_artikal' AND id_pj='$id_pj')

Is it possible that those rows are NOT locked in this scenario?

Because, only in this table I got inconsistent values when there is concurrent SELECT - UPDATE query. Query is executed without error, but values are NOT updated as they should.

Solution

  • No, they are not locked. When you haven't changed the transaction isolation level, it's still the default of REPEATABLE-READ.
    That means that phantom reads are possible. I've written a short explanation of it in this answer.

    What you should do is

    START TRANSACTION;
SELECT … FROM table WHERE … FOR UPDATE;
UPDATE TABLE SET …. WHERE …;
COMMIT;

    Read more about SELECT ... FOR UPDATE here.