Mysql and concurrency

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View

I want to ask a couple of questions to forestall problems on some code I
will be writing later this year.

Basically its about how mysql handles concurrency,, and so when tables
need or don't need locking on update.

If two users from two processes post 'simultaneously' (interpret that in
the way most likely to cause trouble) updates to a table via the mysql
daemon, is it always guaranteed that one transaction if it comprises a
single mysql update or insert statement, will be completed before the
other starts?

e.g. two queries that are of the shape 'update table X set field1=a,
field2=b etc etc' will NEVER result in the value in field 1 being from
one query and in field 2 from the other?

The second question is, if two inserts are posted more or less
simultaneously, does Mysql track the 'last inserted ID' and associate it
with the actual process that did the calling? Or simply deliver up the
last of ANY ID that has been inserted.

This could be an issue for me, since I assume that if I have just
inserted a record, I can pick up its ID that way, but if someone else
does the same in between my insertion and the pick-up..deep trouble.
Needs a lock.?

The final question is how 'atomic;' a query is. If a single SQL
statement is sent to do an update, is it true to say that an
asynchronous read of the data being updated will either return the data
BEFORE or AFTER updating in its entirety? I.e. there is no chance of
getting a 'half updated' record..

Re: Mysql and concurrency - sorry wrong NG - IGNORE

Please ignore this..was intended for mysql NG. Fat finger syndrome.

The Natural Philosopher wrote:
Quoted text here. Click to load it

Site Timeline