| Bug #85431 | Execute “ if(not exists (select ..))” locked next record | ||
|---|---|---|---|
| Submitted: | 14 Mar 2017 7:05 | Modified: | 24 Mar 2017 7:50 |
| Reporter: | zte zte | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
| Version: | mysql5.6.26 | OS: | Red Hat |
| Assigned to: | CPU Architecture: | Any | |
[20 Mar 2017 14:44]
MySQL Verification Team
Hi, I'm not able to reproduce this. Initially I assumed that t, t7 and t5 are mistakes and that you wanted to have t in all those places so create table t(id1 int,id2 int,id3 int, unique key(id2,id3)); insert into t values(1,1,1); insert into t values(20,20,20); and to have in log TABLE LOCK table `yzs`.`t` trx id 275844 lock mode IS but this I can't reproduce. Now if these are supposed to be 3 different tables, then I don't understand the bug report so can you please elaborate in details best regards Bogdan
[21 Mar 2017 0:51]
zte zte
1、Yes,there is a mistake.It should be 't' in all all those places. 2、Besides,the isolation level is "REPEATABLE-READ". 3、The test case is the same： create table t(id1 int,id2 int,id3 int, unique key(id2,id3)); insert into t values(1,1,1); insert into t values(20,20,20); start transaction; call p; then excute "show engine innodb status\G" in another session Thanks, zte
[22 Mar 2017 15:24]
MySQL Verification Team
Hi!
This is actually the expected behavior, because EXISTS() presumes that this is is a nested query.
Change your procedure to be like this and let us know the result:
delimiter //
create procedure p()
begin
DECLARE tot_count INT;
SELECT COUNTI(*) INTO tot_count FROM t WHERE id2=6 AND id3=6;
IF tot_count = 0 THEN
SELECT "not exist";
END IF
end//
Let us know if this variant produces the same lock.
[23 Mar 2017 1:44]
zte zte
Hi, This variant produces no lock. mysql> show variables like '%tx%'; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | tx_isolation | REPEATABLE-READ | | tx_read_only | OFF | +---------------+-----------------+ 2 rows in set (0.00 sec) mysql> delimiter ; mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> call p; +-----------+ | not exist | +-----------+ | not exist | +-----------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> show engine innodb status\G ------------ TRANSACTIONS ------------ Trx id counter 279668 Purge done for trx's n:o < 279665 undo n:o < 0 state: running but idle History list length 619 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 279667, ACTIVE 36 sec MySQL thread id 4, OS thread handle 0x7ff5f039e700, query id 74 localhost root init show engine innodb status Trx read view will not see trx with id >= 279668, sees < 279668
[23 Mar 2017 13:41]
MySQL Verification Team
Hi! If my variant of the stored routine does not produce locks, then this is expected behavior. Locks are necessary with the nested query, which is documented in our manual. Thanks.
[24 Mar 2017 7:50]
zte zte
Hi,Sinisa Could you point out which page of the manual tell this ? Thanks very much.
[24 Mar 2017 16:47]
MySQL Verification Team
Hi ! Information that you seek, you will find in the entire chapters 9,2, 9.5, 14.2, 15.5, 15.6, 15.17 and 15.21. Giving page number is impossible as manual changes on daily basis.
Description: 1、create table t(id1 int,id2 int,id3 int, unique key(id2,id3)); 2、insert into t values(1,1,1); insert into t7 values(20,20,20); 3、create a procedure: delimiter // create procedure p() begin if(not exists (select 1 from t where id2=6 and id3=6)) then select "not exist"; end if; end// 4、start transaction; call p; 5、then excute "show engine innodb status\G" in another session： ... ------------ TRANSACTIONS ------------ ... TABLE LOCK table `yzs`.`t5` trx id 275844 lock mode IS RECORD LOCKS space id 127 page no 4 n bits 72 index `id2` of table `yzs`.`t` trx id 275844 lock mode S locks gap before rec Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 80000014; asc ;; 1: len 4; hex 80000014; asc ;; 2: len 6; hex 000000000886; asc ;; ... 6、acorrding to the result of "show engine innodb status" we can find that select without "lock share in mode or for update" has S lock in the second index record "20-20" How to repeat: See description.