Rohit Bajaj
asked on
Innodb seems to be having table level locking
Hi,
On mysql docs pages i see that innodb has row level locking. But i tried the following on my mysql.
openend mysql on two terminals so there were two different connections.
In one i set autocommit = 0
update accounts
set amount = 100
where name = "rohit";
Now initially the amount for rohit is 90
In the other terminal if i type the following query :
select * from accounts where name = "rohit"
It show 90 because the transaction on the other connection has not been committed;
but if now i type
update accounts
set amount = 200
where name = "mohit"
this halts here.
And if i type commit on the 1st terminal the 2nd terminal command executed. otherwise it just halts probably queued.
That means the table was locked...
Please help me understand the above behavior
Thanks
On mysql docs pages i see that innodb has row level locking. But i tried the following on my mysql.
openend mysql on two terminals so there were two different connections.
In one i set autocommit = 0
update accounts
set amount = 100
where name = "rohit";
Now initially the amount for rohit is 90
In the other terminal if i type the following query :
select * from accounts where name = "rohit"
It show 90 because the transaction on the other connection has not been committed;
but if now i type
update accounts
set amount = 200
where name = "mohit"
this halts here.
And if i type commit on the 1st terminal the 2nd terminal command executed. otherwise it just halts probably queued.
That means the table was locked...
Please help me understand the above behavior
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.