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

            Ascending order - Click to sort in descending order
            brbrbr Bram created issue -
            brbrbr Bram made changes -
            Field Original Value New Value
            brbrbr Bram made changes -
            Summary Using Spatioal Indexes results in Update locks cannot be acquired during a READ UNCOMMITTED transaction Using Spatial Indexes results in Update locks cannot be acquired during a READ UNCOMMITTED transaction
            serg Sergei Golubchik made changes -
            Assignee Marko Mäkelä [ marko ]
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            Component/s GIS [ 10105 ]
            Component/s Storage Engine - InnoDB [ 10129 ]
            Affects Version/s 10.4.20 [ 25733 ]
            Affects Version/s 10.3.30 [ 25732 ]
            Affects Version/s 10.2.39 [ 25731 ]
            Affects Version/s 10.6.1 [ 24437 ]
            danblack Daniel Black made changes -
            serg Sergei Golubchik made changes -
            serg Sergei Golubchik made changes -
            elenst Elena Stepanova made changes -
            Fix Version/s 10.2 [ 14601 ]
            Fix Version/s 10.3 [ 22126 ]
            Fix Version/s 10.4 [ 22408 ]
            Fix Version/s 10.5 [ 23123 ]
            Fix Version/s 10.6 [ 24028 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 123372 ] MariaDB v4 [ 142983 ]
            serg Sergei Golubchik made changes -
            Priority Major [ 3 ] Critical [ 2 ]
            marko Marko Mäkelä made changes -
            Assignee Marko Mäkelä [ marko ] Vladislav Lesin [ vlad.lesin ]
            vlad.lesin Vladislav Lesin made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            ralf.gebhardt Ralf Gebhardt made changes -
            Fix Version/s 10.2 [ 14601 ]
            peterxu Peter Xu made changes -
            Attachment mariadb-server-10.4_10.4.19+maria~focal_amd64.deb [ 65062 ]
            Attachment mariadb-server-core-10.4_10.4.19+maria~focal_amd64.deb [ 65063 ]
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 10.3 [ 22126 ]
            vlad.lesin Vladislav Lesin made changes -
            Status In Progress [ 3 ] Stalled [ 10000 ]
            julien.fritsch Julien Fritsch made changes -
            Priority Critical [ 2 ] Major [ 3 ]
            julien.fritsch Julien Fritsch made changes -
            Priority Major [ 3 ] Critical [ 2 ]
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 10.4 [ 22408 ]
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -

            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.