MYSQL, SQL select for update concept

Why don't we just try it?

Set up the database

CREATE DATABASE so1;
USE so1;
CREATE TABLE notification (`id` BIGINT(20), `date` DATE, `text` TEXT) ENGINE=InnoDB;
INSERT INTO notification(id, `date`, `text`) values (1, '2011-05-01', 'Notification 1');
INSERT INTO notification(id, `date`, `text`) values (2, '2011-05-02', 'Notification 2');
INSERT INTO notification(id, `date`, `text`) values (3, '2011-05-03', 'Notification 3');
INSERT INTO notification(id, `date`, `text`) values (4, '2011-05-04', 'Notification 4');
INSERT INTO notification(id, `date`, `text`) values (5, '2011-05-05', 'Notification 5');

Now, start two database connections

Connection 1

BEGIN;
SELECT * FROM notification WHERE `date` >= '2011-05-03' FOR UPDATE;

Connection 2

BEGIN;

If MySQL locks all rows, the following statement would block. If it only locks the rows it returns, it shouldn't block.

SELECT * FROM notification WHERE `date` = '2011-05-02' FOR UPDATE;

And indeed it does block.

Interestingly, we also cannot add records that would be read, i.e.

INSERT INTO notification(id, `date`, `text`) values (6, '2011-05-06', 'Notification 6');

SELECT FOR UPDATE IS LIKE UPDATE, 
IT LOCKS ALL ROWS SELECTED, AND PREVENT THEM FROM OTHER UPDATE, INSERT AND SELECT FOR UPDATE
IT DOES NOT LOCK ROWS FOR SELECT ONLY
SELECT FOR UPDATE WILL LOCK ALL ROWS IN TABLE IF THE SELECTED COLUMN IS NOT INDEXED EVEN WITH A WHERE CONDITION
IN THIS EXAMPLE SINCE `date` COLUMN IS NOT INDEXED(NOT A KEY), ALL ROWS WITH `date`COLUMN ARE LOCKED
THE IDEA IS THAT IF A COLUMN IS NOT INDEXED, THE DB ENGINE LOCKS THE ROWS AS IT GOES TO CHECK THE WHERE CONDITION.IT HAS TO START WITH THE VERY FIRST IF NOT INDEXED






https://stackoverflow.com/questions/6066205/when-using-mysqls-for-update-locking-what-is-exactly-locked