Category: MySQL

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;

–

Mr. Kanti Kalyan Arumilli

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.

There could be several reasons for working with MySQL from command prompt such as via SSH etc… This is a brief tutorial on some important mysql commands.

Connect by issuing the following command:

> mysql -u root -p

Then you might be prompted for password and after entering the correct password, MySQL shell would be accessible.

The following are some important commands for viewing databases, creating databases, creating users, and providing privileges. The commands are pretty much self-explanatory:

> show databases; -- Shows databases

> use <DATABASE_NAME>; -- Uses database, i.e next set of commands would be against the database, can be used on a necessity basis

> show tables; -- Viewing existing tables in a particular schema

> CREATE DATABSE <DATABASE_NAME>; - Creates a new schema

> CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'password'; -- Creates a new user with userid of "new_user" and password of "password"

> GRANT PRIVILEGE ON database.table TO 'username'@'localhost'; -- Grants privileges on the specific table in the specific database

> GRANT PRIVILEGE ON database.* TO 'username'@'localhost'; -- Grants privileges on all tables in the specific database

> FLUSH PRIVILEGES; -- Not necessary but for the purpose of completeness

> exit; -- Exiting MySQL shell

MariaDB also has the same syntax.

Hoping this blog post helps someone.