0

We got locks in DB without parallel update operation, one exclusive and number of shared. Here are tables:

create table a (
key1 int,
key2 long,
key3 tinyint,
val text,
primary key(key1, key2, key3)
);

create table b (
key1 int,
key2 long,
anotherval text,
primary key(key1, key2)
);

key1 and key2 in tables are identical in names and values, and there is smth about 1bln records, with 4000 unique key1 in tables. key3 is archaic, and is not neccessary at all, cause the pair key1+key2 is unique itself. But we cannot simply erase it - too many records.

Now realize we perform operations in parallel:

select a.val, b.anotherval 
from a left join b using (key1, key2) 
where key1 = 123 and key2 >=100000 and key2 <900000;

and 

delete from a where key1=123 and key2 >300000;

so can this generate such lock?

daemon
  • 31
  • 1
  • 8
  • `DELETE` command for sure can generate a lock – Alex Sep 02 '15 at 15:45
  • What's the value of `tx_isolation` for each of them? Perhaps `READ UNCOMMITTED` is needed for the `SELECT`. – Rick James Sep 02 '15 at 23:48
  • @RickJames current level is `REPEATABLE-READ`, default for innodb. – daemon Sep 03 '15 at 07:43
  • @Alex : as i know delete will cause X lock, but i didn't find in doc that it should block next selects (i'm wrong?). We see that delete is trying to perform about 50 sec and then dropped by timeout. At this moment we see locks. What is interesting - the values fot key1+key2 in locks and in delete are different at moment we observe them. – daemon Sep 03 '15 at 07:54
  • PS. The code calling delete (and next insert) is written so, that only one of delete/insert can be performed at time. They cannot be called in parallel. – daemon Sep 03 '15 at 07:55
  • `DELETE` lock the table on MyISAM and could lock only records when InnoDB but you have to set that properly. so until `DELETE` finished table is locked. read more here http://stackoverflow.com/a/918092/4421474 – Alex Sep 03 '15 at 13:22
  • @Alex. Ok, but why then `delete` is blocked itself? It is droped by timeout even on request to delete the data outside the key2 values (so there is no real data to delete, and it is seen to engine on the stage of key analyze) – daemon Sep 03 '15 at 13:33
  • again... LOCK belongs to the TABLE so since table is LOCKed you have timeouts. If in case you run DELETE and there is no LOCK and there is 0 records to delete it should take just a moment (~0.001s) or not longer then just SELECT command with the same `WHERE` clause. So if you are sure that table is free and there is nothing to delete but your DELETE command takes longer then *moment* or longer then `SELECT` - then you have to investigate because it is almost impossible – Alex Sep 03 '15 at 13:38
  • Suggest you [_delete in chunks_](http://mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks). – Rick James Sep 03 '15 at 20:04

0 Answers0