Re: Mysql and concurrency - sorry wrong NG - IGNORE

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

The Natural Philosopher wrote:

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..