• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 459
  • Last Modified:

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!
0
krupini
Asked:
krupini
  • 5
  • 2
  • 2
2 Solutions
 
NovaDenizenCommented:
If two tried to update the same row at the same time, one of them would get there first and the second one would be forced to wait a moment until the first was done.  Neither would experience an error, only a momentary delay.
0
 
ellandrdCommented:
Hi,

Database transactions cannot occur concurrently - that is only one transaction can get executed at a time.  So in terms of your program updating the same record twice at the same time isnt going to happen - not possible!

And like NovaDenizen said, the delay would be so slight you wont notice it so you've nothing to worry about.

Ellandrd
0
 
krupiniAuthor Commented:
I see. I know that it can't happen at the same time, but I'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. Sounded crazy to me thats why I wanted to confirm.

Thanks!
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
ellandrdCommented:
>>'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...
0
 
NovaDenizenCommented:
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.
0
 
ellandrdCommented:
>>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
0
 
krupiniAuthor Commented:
I was writing my next question http://www.experts-exchange.com/Databases/Mysql/Q_21846854.html while you already answered it!. You can copy and paste your answeres there.
0
 
ellandrdCommented:
what was the solution?
0
 
ellandrdCommented:
answered your next question... ;-)
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 5
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now