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

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

          duplicates

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26204 Update locks cannot be acquired during a READ UNCOMMITTED transaction with SELECT

          • Major - Major loss of function.
          • Closed
          is caused by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25594 Crash in deadlock checker under high load

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          is duplicated by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-35742 Predicates under spatial index cause transaction errors

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-14059 InnoDB assertion failure offset >= ((38U + 36 + 2 * 10) + 5) at page0page.h line 318

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-15275 innodb_gis.rtree_purge failed in buildbot with timeout

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-15284 innodb_gis.rtree_concurrent_srch fails with COUNT(*) mismatch

          • Major - Major loss of function.
          • Confirmed
          links to

          Web Link Stack Overflow question of affected users

          Activity

            Transition Time In Source Status Execution Times
            Vladislav Lesin made transition -
            Open In Progress
            		279d 9h 48m 1
            Vladislav Lesin made transition -
            In Progress Stalled
            		562d 17h 26m 1

            People

              vlad.lesin Vladislav Lesin
              brbrbr Bram
              Votes:
              16 Vote for this issue
              Watchers:
              24 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.