Как получить deadlock определённого типа?

-- Транзакция №1
BEGIN;
SELECT * FROM `testlock` WHERE id=1 LOCK IN SHARE MODE; /* GET S LOCK */
SELECT SLEEP(5);
SELECT * FROM `testlock` WHERE id=1 FOR UPDATE; /* TRY TO GET X LOCK */
COMMIT;
-- Транзакция №2
BEGIN;
SELECT * FROM `testlock` WHERE id=1 FOR UPDATE; /* TRY TO GET X LOCK - DEADLOCK AND ROLLBACK HERE */
COMMIT;

-- Транзакция №1
BEGIN;
SELECT * FROM `testlock` WHERE id=1 FOR UPDATE; /* GET X LOCK */
SELECT SLEEP(5);
SELECT * FROM `testlock` WHERE id=3 FOR UPDATE; /* TRY TO GET X LOCK */
COMMIT;
-- Транзакция №2
BEGIN;
SELECT * FROM `testlock` WHERE id > 2 LOCK IN SHARE MODE; /* TRY TO GET S LOCK*/
COMMIT;

LOCK IN SHARE MODE не запрещает читать. Потому второй SELECT * FROM testlock WHERE id=3 FOR UPDATE; выполняется нормально.

Проделайте следующее - откройте три консоли, чтобы видеть их одновременно. Выполните:

подготовка

create table testlock (id int, val int);
insert into testlock (id,val)
select 1,1 union all
select 2,2 union all
select 3,3 union all
select 4,4 ;

консоль 1

start transaction;
SELECT * FROM `testlock` WHERE id=1 FOR UPDATE;

консоль 2

start transaction;
SELECT * FROM `testlock` WHERE id > 2 LOCK IN SHARE MODE;

консоль 1

SELECT * FROM `testlock` WHERE id=3 FOR UPDATE;

консоль 3

update testlock set val=val+1 where id=3;

консоль 1

commit;

консоль 2

commit;

У меня получилось повторить deadlock именно такого же типа.

Для этого создаётся таблица:

CREATE TABLE ad_data(
   DAY DATE NOT NULL,
   ad_id INT NOT NULL,
   CLIENT INT NOT NULL,
   clicks INT NOT NULL,
   cost INT NOT NULL,
   PRIMARY KEY(DAY, ad_id)
) ENGINE=INNODB;
INSERT INTO ad_data(DAY, ad_id, CLIENT, clicks, cost)
   VALUES
   ('2006-08-01', 1, 1, 10, 100),
   ('2006-08-01', 2, 1, 20, 200),
   ('2006-08-01', 3, 1, 30, 300),
   ('2006-08-01', 4, 1, 40, 400),
   ('2006-08-01', 6, 1, 60, 600);

И в 1-ом соединении выполняются запросы:

START TRANSACTION;
INSERT INTO ad_data(DAY, ad_id, CLIENT, clicks, cost)
   VALUES
   ('2006-08-01', 7, 1, 70, 700);

Далее во 2-ом соединении выполняются запросы:

START TRANSACTION;
DROP TABLE IF EXISTS cost;
create temporary table cost as
select * from ad_data
where day = '2006-08-01';

После этого 2-ое соединении блокируется (в ожидании своей S-блокировки). И в завершении в 1-ом соединении выполняем запрос:

INSERT INTO ad_data(DAY, ad_id, CLIENT, clicks, cost)
   VALUES
   ('2006-08-01', 5, 1, 50, 500);

Получаем дэдлок:

Error Code: 1213

Deadlock found when trying to get lock; try restarting transaction

После вызова команды SHOW ENGINE INNODB STATUS; можно увидеть, что тип deadlock'a именно тот, который был нужен:

------------------------
LATEST DETECTED DEADLOCK
------------------------
170329 18:05:28
*** (1) TRANSACTION:
TRANSACTION 0 187654012, ACTIVE 2 sec, OS thread id 2304 fetching ROWS
mysql TABLES IN USE 2, locked 2
LOCK WAIT 4 LOCK struct(s), HEAP size 320, 7 ROW LOCK(s), UNDO LOG entries 5
MySQL thread id 1180, QUERY id 1984999 127.0.0.1 USER Sending DATA
CREATE TEMPORARY TABLE cost AS
SELECT * FROM ad_data
WHERE DAY = '2006-08-01'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS SPACE id 0 page NO 196086 n bits 80 INDEX `PRIMARY` of TABLE `db`.`ad_data` 
trx id 0 187654012 LOCK MODE S waiting
Record LOCK, HEAP NO 7 PHYSICAL RECORD: n_fields 7; ...
*** (2) TRANSACTION:
TRANSACTION 0 187654007, ACTIVE 6 sec, OS thread id 488 inserting, thread declared inside INNODB 500
mysql TABLES IN USE 1, locked 1
4 LOCK struct(s), HEAP size 320, 4 ROW LOCK(s), UNDO LOG entries 1
MySQL thread id 1179, QUERY id 1985000 ip USER UPDATE
INSERT INTO ad_data(DAY, ad_id, CLIENT, clicks, cost)
   VALUES
   ('2006-08-01', 5, 1, 50, 500)
*** (2) HOLDS THE LOCK(S): 
RECORD LOCKS SPACE id 0 page NO 196086 n bits 80 INDEX `PRIMARY` of TABLE `db`.`ad_data` 
trx id 0 187654007 lock_mode X LOCKS rec but NOT gap
Record LOCK, HEAP NO 7 PHYSICAL RECORD: n_fields 7; ...
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS SPACE id 0 page NO 196086 n bits 80 INDEX `PRIMARY` of TABLE `db`.`ad_data` 
trx id 0 187654007 lock_mode X LOCKS rec but NOT gap waiting
Record LOCK, HEAP NO 8 PHYSICAL RECORD: n_fields 7; ...
*** WE ROLL BACK TRANSACTION (2)
------------

То есть:

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
... LOCK MODE S waiting
*** (2) HOLDS THE LOCK(S): 
... lock_mode X LOCKS rec but NOT gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
...lock_mode X LOCKS rec but NOT gap waiting