Customers who sign-up prior to 30/06/2024 get unlimited access to free features, newer features (with some restrictions), but for free for at least 1 year.Sign up now! https://webveta.alightservices.com/
Categories
MySQL

MySQL “FOR UPDATE” And “SKIP LOCKED” clauses

MySQL has two special clauses that are very useful in certain scenarios.

FOR UPDATE:

In certain scenarios, we need atomicity i.e we try to lock certain rows, do something and then update the set of rows. FOR UPDATE clause is specifically for this reason.

Example:

START TRANSACTION;

SELECT * FROM table WHERE col1 BETWEEN 1 AND 5
FOR UPDATE;

....

COMMIT;

At this point whatever rows match the criteria, would be locked, the rows can be updated as per needs and COMMIT or ROLLBACK.

However, if there are other queries running in parallel, the performance might be slightly affected. Specifically, in large-scale multi-threaded applications. For this we would use SKIP LOCKED, i.e the other query would run, skipping the rows that were locked.

Now combing these two concepts, we can write a sql statement that locks some rows based on WHERE criteria and other sql statements can still continue happen.

START TRANSACTION;

SELECT * FROM table WHERE col1 BETWEEN 1 AND 5
FOR UPDATE SKIP LOCKED;

....

COMMIT;

I don’t have any fake aliases, nor any virtual aliases like some of the the psycho spy R&AW traitors of India. NOT associated – “ass”, eass, female “es”, “eka”, “ok”, “okay”, “is”, “erra”, yerra, karan, kamalakar, diwakar, kareem, karan, sowmya, zinnabathuni, bojja srinivas (was a friend and batchmate 1998 – 2002), mukesh golla (was a friend and classmate 1998 – 2002), thota veera, uttam’s, bandhavi’s, bhattaru’s, thota’s, bojja’s, bhattaru’s or Arumilli srinivas or Arumilli uttam (may be they are part of a different Arumilli family – not my family).

Mr. Kanti Kalyan Arumilli

Arumilli Kanti Kalyan, Founder & CEO
Arumilli Kanti Kalyan, Founder & CEO

B.Tech, M.B.A

Facebook

LinkedIn

Threads

Instagram

Youtube

Founder & CEO, Lead Full-Stack .Net developer

ALight Technology And Services Limited

ALight Technologies USA Inc

Youtube

Facebook

LinkedIn

Phone / SMS / WhatsApp on the following 3 numbers:

+91-789-362-6688, +1-480-347-6849, +44-07718-273-964

+44-33-3303-1284 (Preferred number if calling from U.K, No WhatsApp)

kantikalyan@gmail.com, kantikalyan@outlook.com, admin@alightservices.com, kantikalyan.arumilli@alightservices.com, KArumilli2020@student.hult.edu, KantiKArumilli@outlook.com and 3 more rarely used email addresses – hardly once or twice a year.