An update lock cannot be acquired during a READ UNCOMMITTED transaction - Source

Revision: 137057
User: Pascal Boulesteix
Date: 2024-03-20 07:03

Hello everyone
I just upgraded to the latest version of WampServer to use PHP 8. Before this installation, I used MySQL (by default) but I took advantage of this installation to use MariaDB (like for my website).

During my non-regression tests, I encountered the following problem: Unable to execute a query with a subquery using a join on spatial data with another table.

When I execute this query (this type of query in fact because I have already had the case), the message "An update lock cannot be acquired during a READ UNCOMMITTED transaction" sanctions the execution.

Note that if I transform the subquery into a temporary table, the query execution works. Also note that with MySQL there was no problem

An idea ?

SELECT
     SITE.set_name,
     SITE.site_name,
     wnat_gps.unik_lieudit AS unik_lieudit,
     wnat_gps.unik AS unik_gps, 'G' AS Type_Localite,
point(0,0) AS lieudit_p_coordonnees_spacial,
     wnat_gps.p_coordonnees_spacial AS gps_p_coordonnees_spacial,
     ST_ASTEXT(ST_CENTROID(SITE.g_coordinates_spacial)) AS Centroid
   FROM (
   SELECT
   wnat_geometries_objets.unik AS unik_geometrie_objet,
   wnat_geometries_ensembles.unik_organism,
   wnat_geometries_ensembles.unik_territoire,
   organisms.Organism,
   wnat_geometries_sets.set_name,
   wnat_geometries_sites.site_name,
   wnat_geometries_objets.g_coordonnees_spacial,
   wnat_geometries_sites.unik_geometrie_ensemble,
   wnat_geometries_objets.unik_geometrie_site
FROM wnat_geometries_sites
   INNER JOIN wnat_geometries_ensembles
     ON wnat_geometries_sites.unik_geometrie_ensemble = wnat_geometries_ensembles.unik
   INNER JOIN wnat_geometries_objets
     ON wnat_geometries_objets.unik_geometrie_site = wnat_geometries_sites.unik
   INNER JOIN organisms
     ON wnat_geometries_ensembles.unik_organism = organisms.unik
WHERE wnat_geometries_ensembles.ensemble_name = 'CDL Corse (PI)'
) SITE,
        wnat_gps
   WHERE ST_CONTAINS(SITE.g_coordonnees_spacial, wnat_gps.p_coordonnees_spacial)

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.

An update lock cannot be acquired during a READ UNCOMMITTED transaction - Source

Products

Services

Pricing

Resources

About Us

Download MariaDB