Why MySQL is written in if not exists with caution

This article will explain in detail why MySQL is written in if not exists. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

Version 5.6.14

Environment

CREATE TABLE `t1` (

`id`int (11) NOT NULL

`batchid` int (11) DEFAULT NULL

`state` int (11) DEFAULT NULL

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

INSERT INTO `t1` (`id`, `batchid`, `state`) VALUES (1pd1)

INSERT INTO `t1` (`id`, `batchid`, `state`) VALUES

Drop procedure if exists p

Delimiter $$

CREATE PROCEDURE `p` ()

Begin

If not exists (select * from T1 where id=1) then

Select 'id=1 not exists'

End if

End $$

Delimiter

According to the following timeline, window 2 is actually blocked.

View show engine innodb status

The following information is found:

-TRANSACTION 15252974, ACTIVE 2 sec starting index read

Mysql tables in use 1, locked 1

LOCK WAIT 2 lock struct (s), heap size 376,1 row lock (s)

MySQL thread id 38, OS thread handle 0x1d88, query id 4663 localhost 127.0.0.1 root statistics

Select * from T1 where id=1

LIMIT 0, 1000

For update

-TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 2344 page no 3 n bits 72 index `PRIMARY` of table `mvbox`.`t1` trx id 15252974 lock_mode X locks rec but not gap waiting

Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0

0: len 4; hex 80000001; asc

1: len 6; hex 000000e8bdc4; asc

2: len 7; hex 030003c0100437; asc 7

3: len 4; hex 80000001; asc

4: len 4; hex 80000001; asc

-

-TRANSACTION 15252973, ACTIVE 15 sec

2 lock struct (s), heap size 376,1 row lock (s)

MySQL thread id 36, OS thread handle 0x35c, query id 4615 localhost 127.0.0.1 root cleaning up

During the process, if not exists's query was unexpectedly locked.

This is the end of the article on "Why MySQL is written cautiously in if not exists". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.