Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-26123

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

    XMLWordPrintable

    Details

      Description

      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

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              marko Marko Mäkelä
              Reporter:
              brbrbr Bram
              Votes:
              6 Vote for this issue
              Watchers:
              11 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration