Bug #121636 InnoDB: prevent phantoms in FULLTEXT locking reads
Description:
A FULLTEXT locking read does not protect its predicate. At SERIALIZABLE (or with FOR SHARE / FOR UPDATE at any isolation level) MATCH ... AGAINST S-locks only the rows it returns (PRIMARY and FTS_DOC_ID_INDEX records). The inverted index has no equivalent of B-tree next-key locks or R-tree predicate locks, so a concurrent transaction can insert a matching document and commit:
-- both sessions: SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE
-- s1
START TRANSACTION;
SELECT id FROM doc WHERE MATCH(body) AGAINST('apple'); -- {2}
-- s2
INSERT INTO doc VALUES (3, 'apple crumble'); COMMIT; -- does not block
-- s1
SELECT id FROM doc WHERE MATCH(body) AGAINST('apple'); -- {2, 3}: phantom
COMMIT;
This makes write skew through a FULLTEXT predicate possible: reader reads MATCH and writes a marker row, writer reads the marker and inserts a matching document; both commit. The same shape with a B-tree range or a SPATIAL predicate correctly deadlocks one of the two.
SELECT COUNT(*) ... WHERE MATCH(...) is worse: it goes through the read_just_key path and takes no InnoDB locks at all, even on the returned rows.
How to repeat:
Setup (any session):
CREATE DATABASE IF NOT EXISTS test; USE test;
DROP TABLE IF EXISTS doc;
CREATE TABLE doc (id INT PRIMARY KEY, body TEXT, k INT, FULLTEXT(body), KEY(k)) ENGINE=InnoDB;
INSERT INTO doc VALUES (1,'banana split',10),(2,'apple pie',20);
Session 1:
USE test;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
SELECT id FROM doc WHERE MATCH(body) AGAINST('apple'); -- returns 2
Session 2:
USE test;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
INSERT INTO doc VALUES (3,'apple crumble',30); COMMIT; -- BUG: does not block
Session 1:
SELECT id FROM doc WHERE MATCH(body) AGAINST('apple'); -- returns 2,3 -> phantom
COMMIT;
Suggested fix:
Take a table-level S lock in ha_innobase::ft_init_ext() when the read is a locking read (select_lock_type != LOCK_NONE). Writers hold IX on the table, so they wait until the reader commits. Non-locking reads (autocommit SELECT, REPEATABLE READ without FOR SHARE) are not affected. A row_lock_table(prebuilt, lock_mode) overload is added; the existing row_lock_table(prebuilt) becomes a thin wrapper.
The table is the only lockable granule that covers a FULLTEXT predicate today. A finer alternative would be a predicate lock on the query's token set, analogous to the R-tree MBR predicate locks, but FTS has no infrastructure for that and the COUNT(*) path does not even start an InnoDB transaction, so I kept the fix to what can be reviewed in a few lines. Happy to discuss if the coarser lock is considered too heavy for FOR SHARE on REPEATABLE READ.
Two commits, test first: innodb_fts.serializable_phantom encodes the expected (blocking) behaviour and fails without the fix, passes with it. The innodb_fts suite passes with the fix (56/56 runnable tests). Also verified with a Jepsen append run and a set of isolationtester-style two/three-session scenarios against a patched 8.4 build.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.037 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
Description: A FULLTEXT locking read does not protect its predicate. At SERIALIZABLE (or with FOR SHARE / FOR UPDATE at any isolation level) MATCH ... AGAINST S-locks only the rows it returns (PRIMARY and FTS_DOC_ID_INDEX records). The inverted index has no equivalent of B-tree next-key locks or R-tree predicate locks, so a concurrent transaction can insert a matching document and commit: -- both sessions: SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE -- s1 START TRANSACTION; SELECT id FROM doc WHERE MATCH(body) AGAINST('apple'); -- {2} -- s2 INSERT INTO doc VALUES (3, 'apple crumble'); COMMIT; -- does not block -- s1 SELECT id FROM doc WHERE MATCH(body) AGAINST('apple'); -- {2, 3}: phantom COMMIT; This makes write skew through a FULLTEXT predicate possible: reader reads MATCH and writes a marker row, writer reads the marker and inserts a matching document; both commit. The same shape with a B-tree range or a SPATIAL predicate correctly deadlocks one of the two. SELECT COUNT(*) ... WHERE MATCH(...) is worse: it goes through the read_just_key path and takes no InnoDB locks at all, even on the returned rows. How to repeat: Setup (any session): CREATE DATABASE IF NOT EXISTS test; USE test; DROP TABLE IF EXISTS doc; CREATE TABLE doc (id INT PRIMARY KEY, body TEXT, k INT, FULLTEXT(body), KEY(k)) ENGINE=InnoDB; INSERT INTO doc VALUES (1,'banana split',10),(2,'apple pie',20); Session 1: USE test; SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; START TRANSACTION; SELECT id FROM doc WHERE MATCH(body) AGAINST('apple'); -- returns 2 Session 2: USE test; SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; INSERT INTO doc VALUES (3,'apple crumble',30); COMMIT; -- BUG: does not block Session 1: SELECT id FROM doc WHERE MATCH(body) AGAINST('apple'); -- returns 2,3 -> phantom COMMIT; Suggested fix: Take a table-level S lock in ha_innobase::ft_init_ext() when the read is a locking read (select_lock_type != LOCK_NONE). Writers hold IX on the table, so they wait until the reader commits. Non-locking reads (autocommit SELECT, REPEATABLE READ without FOR SHARE) are not affected. A row_lock_table(prebuilt, lock_mode) overload is added; the existing row_lock_table(prebuilt) becomes a thin wrapper. The table is the only lockable granule that covers a FULLTEXT predicate today. A finer alternative would be a predicate lock on the query's token set, analogous to the R-tree MBR predicate locks, but FTS has no infrastructure for that and the COUNT(*) path does not even start an InnoDB transaction, so I kept the fix to what can be reviewed in a few lines. Happy to discuss if the coarser lock is considered too heavy for FOR SHARE on REPEATABLE READ. Two commits, test first: innodb_fts.serializable_phantom encodes the expected (blocking) behaviour and fails without the fix, passes with it. The innodb_fts suite passes with the fix (56/56 runnable tests). Also verified with a Jepsen append run and a set of isolationtester-style two/three-session scenarios against a patched 8.4 build.