[MDEV-31236] Assertion `prebuilt->select_lock_type == LOCK_NONE || (index)->is_spatial()' failed in row_search_mvcc Created: 2023-05-10  Updated: 2023-11-28

Status: Stalled
Project: MariaDB Server
Component/s: Locking, Storage Engine - InnoDB, Stored routines
Affects Version/s: 10.6, 10.8, 10.9, 10.10, 10.11, 11.0
Fix Version/s: 10.6, 10.11, 11.0

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Daniel Black
Resolution: Unresolved Votes: 0
Labels: regression-10.6

Issue Links:
Problem/Incident
is caused by MDEV-13115 Implement SELECT [FOR UPDATE|LOCK IN ... Closed

 Description   

The failure is non-deterministic, run with --repeat=N. It usually fails for me in ~10 attempts on a debug build, but it can vary on different machines and builds.
I have not gotten it to fail faster by increasing the number of rows in the table, but it can be experimented with if it does not fail on some build.
The test case uses SKIP LOCKED clause and it appears important, so it is not applicable to versions below 10.6. Maybe the analysis will reveal that it can be replaced by some older logic.

--source include/have_innodb.inc
 
 
 
CREATE TABLE t (a INT, KEY(a)) ENGINE=InnoDB;
 
INSERT INTO t VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9);
 
 
 
CREATE PROCEDURE sp() SELECT * FROM t FOR UPDATE SKIP LOCKED;
 
 
 
--connect (con1,localhost,root,,)
 
CALL sp;
 
FLUSH TABLES;
 
--send
 
  CALL sp;
 
 
 
--connection default
 
SELECT * FROM t FOR UPDATE SKIP LOCKED;
 
 
 
# Cleanup
 
--connection con1
 
--reap
 
DROP TABLE t;
 
DROP PROCEDURE sp;
 
--disconnect con1

10.6 fe89df42

 
mariadbd: /data/src/10.6/storage/innobase/row/row0sel.cc:5527: dberr_t row_search_mvcc(byte*, page_cur_mode_t, row_prebuilt_t*, ulint, ulint): Assertion `prebuilt->select_lock_type == LOCK_NONE || (index)->is_spatial()' failed.
 
230510 19:49:04 [ERROR] mysqld got signal 6 ;
 
 
 
#9  0x00007f56c8253df2 in __GI___assert_fail (assertion=0x55584af7e440 "prebuilt->select_lock_type == LOCK_NONE || (index)->is_spatial()", file=0x55584af79140 "/data/src/10.6/storage/innobase/row/row0sel.cc", line=5527, function=0x55584af7d8e0 "dberr_t row_search_mvcc(byte*, page_cur_mode_t, row_prebuilt_t*, ulint, ulint)") at ./assert/assert.c:101
 
#10 0x0000555849a8c0a7 in row_search_mvcc (buf=0x6190002f8ac8 "\377", mode=PAGE_CUR_G, prebuilt=0x620000086120, match_mode=0, direction=0) at /data/src/10.6/storage/innobase/row/row0sel.cc:5527
 
#11 0x000055584965f6d5 in ha_innobase::index_read (this=0x61d0009376b8, buf=0x6190002f8ac8 "\377", key_ptr=0x0, key_len=0, find_flag=HA_READ_AFTER_KEY) at /data/src/10.6/storage/innobase/handler/ha_innodb.cc:9103
 
#12 0x000055584966290a in ha_innobase::index_first (this=0x61d0009376b8, buf=0x6190002f8ac8 "\377") at /data/src/10.6/storage/innobase/handler/ha_innodb.cc:9472
 
#13 0x0000555848c2a611 in handler::ha_index_first (this=0x61d0009376b8, buf=0x6190002f8ac8 "\377") at /data/src/10.6/sql/handler.cc:3600
 
#14 0x00005558484ffb1f in join_read_first (tab=0x62b0000c6ee8) at /data/src/10.6/sql/sql_select.cc:22646
 
#15 0x00005558484f7dcd in sub_select (join=0x62b0000c5a88, join_tab=0x62b0000c6ee8, end_of_records=false) at /data/src/10.6/sql/sql_select.cc:21606
 
#16 0x00005558484f5f2f in do_select (join=0x62b0000c5a88, procedure=0x0) at /data/src/10.6/sql/sql_select.cc:21150
 
#17 0x00005558484811fa in JOIN::exec_inner (this=0x62b0000c5a88) at /data/src/10.6/sql/sql_select.cc:4812
 
#18 0x000055584847e6de in JOIN::exec (this=0x62b0000c5a88) at /data/src/10.6/sql/sql_select.cc:4590
 
#19 0x0000555848482b2b in mysql_select (thd=0x62b0000bd218, tables=0x62b0000c4940, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x62b0000c5a58, unit=0x62b0000c1588, select_lex=0x62b0000c42f0) at /data/src/10.6/sql/sql_select.cc:5069
 
#20 0x000055584845277a in handle_select (thd=0x62b0000bd218, lex=0x62b0000c14c0, result=0x62b0000c5a58, setup_tables_done_option=0) at /data/src/10.6/sql/sql_select.cc:559
 
#21 0x00005558483b7583 in execute_sqlcom_select (thd=0x62b0000bd218, all_tables=0x62b0000c4940) at /data/src/10.6/sql/sql_parse.cc:6273
 
#22 0x00005558483a5c5c in mysql_execute_command (thd=0x62b0000bd218, is_called_from_prepared_stmt=false) at /data/src/10.6/sql/sql_parse.cc:3949
 
#23 0x00005558483c24fb in mysql_parse (thd=0x62b0000bd218, rawbuf=0x62b0000c4238 "SELECT * FROM t FOR UPDATE SKIP LOCKED", length=38, parser_state=0x7f56ba4d3a30) at /data/src/10.6/sql/sql_parse.cc:8036
 
#24 0x0000555848398342 in dispatch_command (command=COM_QUERY, thd=0x62b0000bd218, packet=0x629000fb4219 "", packet_length=38, blocking=true) at /data/src/10.6/sql/sql_parse.cc:1896
 
#25 0x0000555848395080 in do_command (thd=0x62b0000bd218, blocking=true) at /data/src/10.6/sql/sql_parse.cc:1409
 
#26 0x0000555848801bdc in do_handle_one_connection (connect=0x608000030338, put_in_cache=true) at /data/src/10.6/sql/sql_connect.cc:1416
 
#27 0x000055584880159d in handle_one_connection (arg=0x6080000038b8) at /data/src/10.6/sql/sql_connect.cc:1318
 
#28 0x000055584945bcf2 in pfs_spawn_thread (arg=0x617000008218) at /data/src/10.6/storage/perfschema/pfs.cc:2201
 
#29 0x00007f56c82a7fd4 in start_thread (arg=<optimized out>) at ./nptl/pthread_create.c:442
 
#30 0x00007f56c83285bc in clone3 () at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:81

Reproducible on 10.6+, including earlier minor releases.

 Comments   
Comment by Marko Mäkelä [ 2023-05-11 ]

I can fairly easily reproduce a core dump of this, but I had no luck with rr. SKIP LOCKED was implemented in MDEV-13115 by danblack. Reassigning to him for investigation.

Perhaps it would be helpful to make the procedure run multiple SELECT statements, and the connection default to do the same. With an rr replay trace it should be easier to understand what is going here and how it should be fixed.

Comment by Daniel Black [ 2023-05-19 ]

The assertion cause is select_lock_type is LOCK_X.

Confirmed it is lock_trx_handle_wait a few lines above (under the skipped locked condition) returning DB_SUCCESS with clust_rec = NULL.

It looks odd purely on description, that lock_trx_handle_wait returns DB_SUCCESS rather than seemingly more accurate DB_SUCCESS_LOCKED_REC, however if it did, the case below where it fails would assert on clust_rec != NULL. The one other caller of lock_trx_handle_wait process DB_SUCCESS as the success on a deadlock resolution.

At the moment I'm of the opinion that its just the assertion that is incorrect now that SKIP LOCKED is there.

This is largely based on the comments that "the record doesn't exist in the read view" means the only real action is to go to the next record.

So a fix of the form below, that may need to be extended for the "LOCK IN SHARE MODE" after I manufacture a similar test case around that too.

diff --git a/storage/innobase/row/row0sel.cc b/storage/innobase/row/row0sel.cc
 
index d1d264a7e8a..92b0dc2c7c5 100644
 
--- a/storage/innobase/row/row0sel.cc
 
+++ b/storage/innobase/row/row0sel.cc
 
@@ -5524,7 +5524,7 @@ row_search_mvcc(
 
                case DB_SUCCESS:
 
                        if (clust_rec == NULL) {
 
                                /* The record did not exist in the read view */
 
-                               ut_ad(prebuilt->select_lock_type == LOCK_NONE
 
+                               ut_ad(prebuilt->select_lock_type == (prebuilt->skip_locked ? LOCK_X : LOCK_NONE)
 
                                      || dict_index_is_spatial(index));
 
                                goto next_rec;

Also possible improvement at this point lock_trx_handle_wait could mark a SKIP LOCK query as a deadlock victim returning DB_DEADLOCK. It should be possible to skip to next record here? What needs to be done to trx to make this happen?

Generated at Thu Feb 08 10:22:19 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.