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

              vlad.lesin Vladislav Lesin
              brbrbr Bram
              Votes:
              15 Vote for this issue
              Watchers:
              23 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.