1

Table Details

SHOW CREATE TABLE foldertable

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,
 KEY `serverToken` (`serverToken`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

Engine status

SHOW ENGINE INNODB STATUS

=====================================
2021-05-16 23:28:11 0x4228 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 25 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 7 srv_active, 0 srv_shutdown, 2720 srv_idle
srv_master_thread log flush and writes: 2727
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 43
OS WAIT ARRAY INFO: signal count 48
RW-shared spins 0, rounds 100, OS waits 32
RW-excl spins 0, rounds 774, OS waits 5
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 100.00 RW-shared, 774.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 28244
Purge done for trx's n:o < 28243 undo n:o < 0 state: running but idle
History list length 25
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 283682528964920, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 283682528960704, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: native aio handle (insert buffer thread)
I/O thread 1 state: native aio handle (log thread)
I/O thread 2 state: native aio handle (read thread)
I/O thread 3 state: native aio handle (read thread)
I/O thread 4 state: native aio handle (read thread)
I/O thread 5 state: native aio handle (read thread)
I/O thread 6 state: native aio handle (write thread)
I/O thread 7 state: native aio handle (write thread)
I/O thread 8 state: native aio handle (write thread)
I/O thread 9 state: native aio handle (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
479 OS file reads, 293 OS file writes, 95 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 4441, node heap has 0 buffer(s)
Hash table size 4441, node heap has 0 buffer(s)
Hash table size 4441, node heap has 0 buffer(s)
Hash table size 4441, node heap has 0 buffer(s)
Hash table size 4441, node heap has 0 buffer(s)
Hash table size 4441, node heap has 0 buffer(s)
Hash table size 4441, node heap has 0 buffer(s)
Hash table size 4441, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 11444640
Log flushed up to   11444640
Pages flushed up to 11444640
Last checkpoint at  11444631
0 pending log flushes, 0 pending chkp writes
65 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 33554432
Dictionary memory allocated 41472
Buffer pool size   1002
Free buffers       746
Database pages     256
Old database pages 0
Modified db pages  0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 335, created 142, written 225
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 256, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=11768, Main thread ID=10680, state: sleeping
Number of rows inserted 3, updated 0, deleted 0, read 146
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 1.08 reads/s
Number of system rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

Points to note

I am always executing second query while first transaction is running. The sleep(10) is there intentionally. The SELECT ... FOR UPDATE is used as an alternative to an update statement. Till now the table is totally empty.

As the session progress the data will be inserted as given below. Nothing has happened at the back of the stage.

Tests

First session

First statement
mysqli_begin_transaction($conn);

$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) ;
Second statement
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 statement
mysqli_begin_transaction($conn);

$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) ;
Second statement
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 statement
mysqli_begin_transaction($conn);

$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) ;
Second statement
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.)

Fourth session

First statement
mysqli_begin_transaction($conn);

$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) ;
Second statement
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 statement
mysqli_begin_transaction($conn);

$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) ;
Second statement
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.)

Sixth session

First statement
mysqli_begin_transaction($conn);

$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) ;
Second statement
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 xamp for testing, phpmyadmin for queries, and postman for transaction API.

How can I understand this strange behaviour?

Summary

where serverToken  = 1234567 FOR  UPDATE  -- Session 1, all cases

Session 2:
12345,   'ABCDE',    First
12345,   'ABCDEf',   Second
1234567, 'ABCDEfg',  Third
123, '', 'ABCDEfgh'  Fourth --fast
1235465, 'ABCEfgh'   Fifth
1235465, 'ABCEfghi', Sixth -- fast

Final Image

enter image description here

8
  • 1
    What behaviour are you considering strange? Note: Don't add a unique index the same as the PK, its not needed. Don't do SELECT ... FOR UPDATE unless you need to do an update. – danblack May 13 at 17:06
  • SELECT FOR UPDATE is used as an alternative of UPDATE . STRANGE IS EXECUTION TIME OF EACH INSERT STATEMENT – Shadow Developer May 13 at 17:11
  • Your code declares two SQL strings but then executes only the second one. Was this an oversight? Please fix. – Rick James May 16 at 17:51
  • SELECT FOR UPDATE says "I might change this row(s); please keep away from them." – Rick James May 16 at 17:52
  • How many rows in the table? Are ABCEfg and ABCEfghi consecutive in the index? If so, insert a row between them (eg ABCEfgh), then see if you still have the problem. (This is to see if there is a "gap lock" involved. Also, provide SHOW ENGINE=innodb STATUS for more info. – Rick James May 16 at 17:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.