Shulou(Shulou.com)05/31 Report--

Editor to share with you what you need to pay attention to when using if not exists in MySQL. I hope you will get something after reading this article. Let's discuss it together.

Environment MySQL 5.6.14

Transaction isolation level read commit

The opening and ending of the transaction is controlled by the JAVA program.

The process of reporting a deadlock last time is abstracted as follows

Delimitr $$

CREATE PROCEDURE `test_ proc` (

Pid int

)

Begin

If not exists (select * from t where id=pid) then

Insert into t (id) values (pid)

End if

Update t set total=total+1 where id=pid

End $$

Delimiter

The reason for the deadlock is understood, that is, in the case of concurrency, Insert encounters an exclusive lock and attempts to add a shared lock.

At the end of the Update, two connections holding a shared lock tried to apply for an exclusive lock, resulting in a deadlock.

But the problem is. How did you get to the last line of the Update statement?

The other two connections, shouldn't there be a Duplicate entry 'xx' for key' PRIMARY' error in the Insert statement Times?

The problem should lie in this structure.

If not exists (select * from t where id=pid) then

Xxx

End if

When using if not exists mode, you should pay attention to it. If an exception occurs in this structure, it will not report an error, but will jump out of the IF judgment and continue to execute!

Experimental preparation

CREATE TABLE `t` (

`id`int (11) NOT NULL

`total`int (11) NOT NULL DEFAULT'0'

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

Truncate table t

Drop procedure if exists test_proc

Delimiter $$

CREATE PROCEDURE `test_ proc` (

Pid int

Ptotal int

)

Begin

If not exists (select * from t where id=pid) then

Insert into t (id,total) value (pid,ptotal)

Update t set total=ptotal+1 where id=pid

End if

Select ptotal+1

End $$

Delimiter

Open three clients and execute the process

The first client executes and is not submitted.

Second, the third client is in a blocking state.

Wait for the first client to submit

The second client returns 201

The third client returns 301

And there is no error message.

After all three clients have submitted, view the T-table information

There is only one record, id is 1, and total is 101

In other words, the second and third client, after getting the exception of the primary key conflict, did not report an error and did not continue to execute the remaining statements within the IF block, but directly jumped out of the IF block and continued to execute the statements outside the IF block!

The wrong place does not report the error, in a large section of storage process, lead to deadlock or minor problems, for fear of causing data confusion, but do not know it.

In view of this situation, if there is a primary key or unique constraint, I think it should simply be changed to the following way.

Delimiter $$

CREATE PROCEDURE `test_ proc` (

Pid int

Ptotal int

)

Begin

Insert into t (id,total) value (pid,ptotal)

Update t set total=ptotal+1 where id=pid

Select ptotal+1

End $$

Delimiter

After reading this article, I believe you have a certain understanding of "what to pay attention to when using if not exists in MySQL". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for your reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.