0

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.

MiTja
  • 61
  • 5

2 Answers2

2

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.

Community
  • 1
  • 1
fancyPants
  • 50,732
  • 33
  • 89
  • 96
1

INDEX(id_artikal), INDEX(id_pj) is not the same as INDEX(id_artikal, id_pj). Add the latter; both queries will run a lot faster.

Yes, the FOR UPDATE is required. This must be added for all cases of START; SELECT...; UPDATE (same row)...; COMMIT. You possibly have errors that you have not noticed!

I would not worry about tx_isolation.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • And this open new level in my mysql life :-) Just read your fantastic text at http://mysql.rjweb.org/doc.php/index_cookbook_mysql Thank you – MiTja Apr 08 '17 at 06:23
  • You are welcome, and thanks for the nice words. You are not alone in finding useful info there. The more people who read that blog, the fewer 'simple' questions will need to be answered on this forum. – Rick James Apr 08 '17 at 14:40