How to solve this inconsistency timing of MySQL queries?


Table Details :-

SHOW CREATE TABLE foldertable

Result

CREATE TABLE `foldertable` (  `serverToken` bigint(1) NOT NULL,  `folderName` varchar(255) NOT NULL,  `folderid` varchar(255) NOT NULL,  `RootFolderPath` longtext NOT NULL,  `createdTime` datetime NOT NULL,  `LastEdited` datetime NOT NULL,  `RootFolderPreviewPath` longtext NOT NULL,  `userFolderPathName` text NOT NULL,  `starred` tinyint(1) NOT NULL,  `trashed` tinyint(1) NOT NULL,  PRIMARY KEY (`folderid`) USING BTREE,  UNIQUE KEY `folderid` (`folderid`),  KEY `serverToken` (`serverToken`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4  

Point to note-

I am always executing second query while first transaction is running . The sleep(10) is there intentionaly

Tests

First session

First Query :-

mysqli_begin_transaction($  conn);  $  sql = "SELECT * FROM foldertable where folderid  = '1234567' FOR  UPDATE" ; $  sql = "SELECT * FROM foldertable where serverToken  = 1234567 FOR  UPDATE" ; mysqli_query($  conn , $  sql) ; echo (mysqli_error($  conn)) ; echo(mysqli_affected_rows($  conn)) ; sleep (10); mysqli_commit($  conn) ;  

2nd Query

INSERT INTO `foldertable`(     `serverToken`,     `folderName`,     `folderid`,     `RootFolderPath`,     `createdTime`,     `LastEdited`,     `RootFolderPreviewPath`,     `userFolderPathName`,     `starred`,     `trashed` ) VALUES(     12345,     '',     'ABCDE',     '',     '',     '',     '',     '',     '',     '' );   

Result

1 row inserted. (Query took 9.4569 seconds.)

Second session

First Query :-

mysqli_begin_transaction($  conn);  $  sql = "SELECT * FROM foldertable where folderid  = '1234567' FOR  UPDATE" ; $  sql = "SELECT * FROM foldertable where serverToken  = 1234567 FOR  UPDATE" ; mysqli_query($  conn , $  sql) ; echo (mysqli_error($  conn)) ; echo(mysqli_affected_rows($  conn)) ; sleep (10); mysqli_commit($  conn) ;  

2nd Query

INSERT INTO `foldertable`(     `serverToken`,     `folderName`,     `folderid`,     `RootFolderPath`,     `createdTime`,     `LastEdited`,     `RootFolderPreviewPath`,     `userFolderPathName`,     `starred`,     `trashed` ) VALUES(     12345,     '',     'ABCDEf',     '',     '',     '',     '',     '',     '',     '' ); 

Result

1 row inserted. (Query took 9.4569 seconds.)

Third session

First Query :-

mysqli_begin_transaction($  conn);  $  sql = "SELECT * FROM foldertable where folderid  = '1234567' FOR  UPDATE" ; $  sql = "SELECT * FROM foldertable where serverToken  = 1234567 FOR  UPDATE" ; mysqli_query($  conn , $  sql) ; echo (mysqli_error($  conn)) ; echo(mysqli_affected_rows($  conn)) ; sleep (10); mysqli_commit($  conn) ;  

2nd Query

INSERT INTO `foldertable`(     `serverToken`,     `folderName`,     `folderid`,     `RootFolderPath`,     `createdTime`,     `LastEdited`,     `RootFolderPreviewPath`,     `userFolderPathName`,     `starred`,     `trashed` ) VALUES(     1234567,     '',     'ABCDEfg',     '',     '',     '',     '',     '',     '',     '' ); 

Result

1 row inserted. (Query took 9.4569 seconds.)

Forth session

First Query :-

mysqli_begin_transaction($  conn);  $  sql = "SELECT * FROM foldertable where folderid  = '1234567' FOR  UPDATE" ; $  sql = "SELECT * FROM foldertable where serverToken  = 1234567 FOR  UPDATE" ; mysqli_query($  conn , $  sql) ; echo (mysqli_error($  conn)) ; echo(mysqli_affected_rows($  conn)) ; sleep (10); mysqli_commit($  conn) ;  

2nd Query

INSERT INTO `foldertable`( `serverToken`, `folderName`, `folderid`, `RootFolderPath`, `createdTime`, `LastEdited`, `RootFolderPreviewPath`, `userFolderPathName`, `starred`, `trashed` ) VALUES( 123, '', 'ABCDEfgh', '', '', '', '', '', '', '' ) 

Result

1 row inserted. (Query took 0.0073 seconds.)

Fifth session

First Query :-

mysqli_begin_transaction($  conn);  $  sql = "SELECT * FROM foldertable where folderid  = '1234567' FOR  UPDATE" ; $  sql = "SELECT * FROM foldertable where serverToken  = 1234567 FOR  UPDATE" ; mysqli_query($  conn , $  sql) ; echo (mysqli_error($  conn)) ; echo(mysqli_affected_rows($  conn)) ; sleep (10); mysqli_commit($  conn) ;  

2nd Query

INSERT INTO `foldertable`( `serverToken`, `folderName`, `folderid`, `RootFolderPath`, `createdTime`, `LastEdited`, `RootFolderPreviewPath`, `userFolderPathName`, `starred`, `trashed` ) VALUES( 1235465, '', 'ABCEfgh', '', '', '', '', '', '', '' ) 

Result

1 row inserted. (Query took 9.5040 seconds.)

Fifth session

First Query :-

mysqli_begin_transaction($  conn);  $  sql = "SELECT * FROM foldertable where folderid  = '1234567' FOR  UPDATE" ; $  sql = "SELECT * FROM foldertable where serverToken  = 1234567 FOR  UPDATE" ; mysqli_query($  conn , $  sql) ; echo (mysqli_error($  conn)) ; echo(mysqli_affected_rows($  conn)) ; sleep (10); mysqli_commit($  conn) ;  

2nd Query

INSERT INTO `foldertable`(     `serverToken`,     `folderName`,     `folderid`,     `RootFolderPath`,     `createdTime`,     `LastEdited`,     `RootFolderPreviewPath`,     `userFolderPathName`,     `starred`,     `trashed` ) VALUES(     1235465,     '',     'ABCEfghi',     '',     '',     '',     '',     '',     '',     '' ); 

Result

1 row inserted. (Query took 0.0087 seconds.)

I am quite new to mysql . I am using xampp for Testing and phpmyadmin for query and postman for transaction api .

So How can I solve this strange behaviour .