Using Indexes results in Update locks cannot be acquired during a READ UNCOMMITTED transaction

user_16377318 0
Viewed 421

After upgrading to mariadb 10.5.11 I ran into a weird problem with the indexes.

Simple table with two colums Type(varchar) and Point(point)

An index on Type(Tindex) and a spatial index on Point(Pindex)

Now a query like

SELECT X(Point) as x,Y(Point) as y,hotels.Type FROM hotels WHERE (Type in ("acco")) AND MBRContains( GeomFromText( 'LINESTRING(4.922 52.909,5.625 52.483)' ), hotels.Point)
;

Results in a

Error in query (1207): Update locks cannot be acquired during a READ UNCOMMITTED transaction

While both

SELECT X(Point) as x,Y(Point) as y,hotels.Type FROM hotels USE INDEX (Pindex) WHERE (Type in ("acco")) AND MBRContains( GeomFromText( 'LINESTRING(4.922 52.909,5.625 52.483)' ), hotels.Point)
;

and

SELECT X(Point) as x,Y(Point) as y,hotels.Type FROM hotels USE INDEX (Tindex) WHERE (Type in ("acco")) AND MBRContains( GeomFromText( 'LINESTRING(4.922 52.909,5.625 52.483)' ), hotels.Point)
;

work fine. As mariadb 10.5.10 did

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

| 1 | SIMPLE | hotels | range|filter | Type,Pindex | Pindex|Type | 34|302 | NULL | 340 (4%) | Using where; Using rowid filter |

mariadb-10.5 mariadb
3 Answers

The issue is now being tracked as MDEV-26123 (I guess you reported it there). The issue description says that the problem was introduced in MariaDB 10.2.39, 10.3.30, 10.4.20, 10.5.11, 10.6.1.

I ran into the issue after upgrading to MariaDB 10.6.4. I downgraded to 10.6.0, which was possible without having to do any migration of the data. It seems to have fixed the problem for now.

user_2423650

The cause of this appears to be the code fix for MDEV-25594.

I cannot see anything in the commit message or discussion there that indicates that a change to the READ UNCOMMITTED behavior was intentional.

There are no open bug reports on this so I recommend you create a new bug report.

user_101951530
user_80379040
Related