CODEWITHSUNDEEP
mysqlsqllocking
sobmortin354
sobmortin354

Reputation: 99

Make MySQL Stored Procedure Low Priority/Not Lock Tables

I have a Stored Procedure in MySQL that locks tables while it is running. It is a fairly heavy procedure that can go on for up to a minute, and other requests that hit the DB during this time cause a deadlock error. Without rewriting the procedure, is there a way to make this Procedure low priority, or not lock the table at this time, so that other requests that come in take priority over it and are not blocked. I wouldn't want to cancel the procedure, but would like it to continue in the background.

I can see some settings like this in the creation of the procedure, but can't find what I need. Is this possible, and how can it be done? These are the settings:

/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection  = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION' */ ;

Upvotes: 1

Views: 281

Answers (1)

Gordan Bobić
Gordan Bobić

Reputation: 1888

Short answer: No, there is no way to make a procedure not lock tables while it is running with only trivial changes.

Additionally, assuming all of your tables are InnoDB (and in 2020, there are vanishingly few reasons for this to not be the case), there should be no table level locking going on unless your procedure is explicitly locking tables.

Upvotes: 1

Related Questions