krupini
asked on
Concurrency control in MySQL 5
Say I have a very simple table, called `table1` with two columns, `id` and `price`.
if I execute "SELECT table1.* FROM table1;" I get
id price
-------------
1 1.0
2 2.0
3 3.0
Now, lets say I write a program that has an infitie loop where it executes "UPDATE table1 SET price=RAND() WHERE id=3;"
Now lets say I have 100 if those programs running at the same time. Is it possible that one of those UPDATEs would fail because the same record is being edited by another program?
Thanks!
if I execute "SELECT table1.* FROM table1;" I get
id price
-------------
1 1.0
2 2.0
3 3.0
Now, lets say I write a program that has an infitie loop where it executes "UPDATE table1 SET price=RAND() WHERE id=3;"
Now lets say I have 100 if those programs running at the same time. Is it possible that one of those UPDATEs would fail because the same record is being edited by another program?
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>'ve heard from someone that as soon as a client tries to update something, it locks that field or a row or a table, and any other client that tries to update would fail because of the lock
yes that person is correct but the time/delay of the lock will not get noticed... it is like around 0.00001 milli seconds...
yes that person is correct but the time/delay of the lock will not get noticed... it is like around 0.00001 milli seconds...
More like a tenth or hundredth of a millisecond.
In mysql, locks usually cause delays, not errors. When something tries to access a locked resource, it doesnt say "Oh no, it's locked, I can't do anything! Error!". Instead it just waits around until the resource becomes unlocked and then does what it intended to do.
In mysql, locks usually cause delays, not errors. When something tries to access a locked resource, it doesnt say "Oh no, it's locked, I can't do anything! Error!". Instead it just waits around until the resource becomes unlocked and then does what it intended to do.
>>More like a tenth or hundredth of a millisecond.
yes i know but i was trying to express that it wont be noticed so i figured krupini would get the drift by the example...
all is good and not harm done
ellandrd
yes i know but i was trying to express that it wont be noticed so i figured krupini would get the drift by the example...
all is good and not harm done
ellandrd
ASKER
I was writing my next question https://www.experts-exchange.com/questions/21846854/Automatic-transactions-concurrency-in-MySQL-5.html while you already answered it!. You can copy and paste your answeres there.
what was the solution?
answered your next question... ;-)
ASKER
Thanks!