vlad.lesin, I introduced that debug check when reimplementing the buffer page latches in MariaDB Server 10.6. The assertion fails because an exclusively held latch is being freed. This is related to the question that was left unanswered in MDEV-14059. Why are buffer page descriptors being copied in some SPATIAL INDEX operations? Could it be avoided?

That is not the only peculiarity in SPATIAL INDEX locking. What are the tweaks in the purge of history needed for? Can some garbage in SPATIAL INDEX be left behind by the purge of history if there was some locking conflict when the purge was attempted? Why is there a race condition between MVCC and ROLLBACK (MDEV-15284)?

I think that we must first understand how the locking on MySQL 5.7 SPATIAL INDEX was supposed to work. This is not an easy task.

Edit: it seems like updating to MariaDB 10.11 LTS and deleting and re-creating the index fixed it for me!

From https://stackoverflow.com/questions/68245557/using-indexes-results-in-update-locks-cannot-be-acquired-during-a-read-uncommitt#comment122162477_68824895

To solve this, I:

1. Upgraded to the latest MariaDB
2. Ran `ALTER TABLE DROP INDEX your_spatial_index; ALTER TABLE ADD SPATIAL(your_column)`

The error seems to stop occurring after that!

---------

Old comment:

I just wanted to mention that we're still running into this issue in 10.4.26. So far, our workaround has been to install an earlier version of the mariadb-server-10.4 packages (10.4.19) and pinning those on apt, since we're on Ubuntu.

Since the MariaDB repositories only keep the latest two versions available, we have a version saved for Ubuntu 20.04 LTS (focal):

• mariadb-server-10.4_10.4.19+maria~focal_amd64.deb
• mariadb-server-core-10.4_10.4.19+maria~focal_amd64.deb

To install these, just place them onto the servers and run:

dpkg -i mariadb-server-10.4_10.4.19+maria~focal_amd64.deb
dpkg -i mariadb-server-core-10.4_10.4.19+maria~focal_amd64.deb
apt-mark hold mariadb-server-10.4
apt-mark hold mariadb-server-core-10.4

I just test latest 10.11 and 11.0-rc using docker images from https://hub.docker.com/_/mariadb and the problem still exist... Tested with the test case from https://jira.mariadb.org/browse/MDEV-26123?focusedCommentId=221221&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-221221

I encountered this error with spatial index on point, and trying to run the following query

CREATE TABLE `profile` (
`location` point NOT NULL DEFAULT point(0,0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `profile`
ADD SPATIAL KEY `profile_location_spatial` (`location`);

insert into profile(location) VALUES(point(41, 12));

SELECT profile.location from profile
WHERE
ST_WITHIN(profile.location, X'SOME INLINE GEOMETRY HERE')

I also tried the latest version of MariaDB with no help.. Latest version I can run now is 10.5.10, but at least 10.4.19 works too

Any updates on this?

I have the same on 11.8.1-MariaDB-ubu2404 - mariadb.org binary distribution

SELECT r.id
FROM records r IGNORE INDEX(coordinates)
JOIN areas a ON ST_Contains(a.coordinates, r.coordinates)
WHERE a.id = 13863 LIMIT 100;

works fine but

SELECT r.id
FROM records r
JOIN areas a ON ST_Contains(a.coordinates, r.coordinates)
WHERE a.id = 13863 LIMIT 100;

gives: Error Code: 1207. Update locks cannot be acquired during a READ UNCOMMITTED transaction

UPDATE: tried the failing query on 8.4.4 - MySQL Community Server - GPL and it works without problem