In Mysql with default Repeatable read isolation level,
In session 1 transaction 1,
start transaction;
update student_details set name='uuu' where dept='mech';
The changes have been made successfully. Here the column 'dept' is indexed.
After this in session 2 transaction 2,
start transaction;
update student_details set name='kkk' where dept='ece';
Here too changes have been made successfully.
But if I do the same without indexing the 'dept' column, then transaction 2 in session 2 waits until the transaction 1 in session 1 gets committed or rollback.
I know that, in repeatable read isolation level when we make a 'update...where...' statement each and every row which is being read is locked with an exclusive lock(write lock).Hence transaction 2 waits until 1 releases its lock.
So, What really happens when we index a column and make a 'update...where..' query? why the same thing is not happening with indexes??
A locking read(select for update), an UPDATE, or a DELETE generally set record locks on every index record that is scanned in the processing of the SQL statement. It does not matter whether there are WHERE conditions in the statement that would exclude the row. InnoDB does not remember the exact WHERE condition, but only knows which index ranges were scanned. The locks are normally next-key locks that also block inserts into the “gap” immediately before the record.
As states MySQL documentation.
Meaning,
if there is an index, it remembers the index, hence row level locking happens.
If it can't find an index, then a range lock on secondary indexes or full table locking could happen.
So in your case with index, row level locking happens, 2nd transaction could go on. But in case when no index, it locks other transaction. Hence need to wait.