concurrent mySql database queries

views:

232

answers:

+1 Q:

concurrent mySql database queries

I'm running mySql server that being updated every 4 hours. In the mean while data can be retrieved.

Does mySql handle this scenario where the DB is being updated and a query from the user is received?

or I should handle this scenario? Is it possible to create a snapshot of the DB just before the update takes place and query this DB?

Thanks

If you are worried about how concurrent writes are handled, that's one thing, but you can safely read from a MySQL database that is being updated by another thread without worrying about corrupted data.

Of course, if the writing thread is performing multiple writes that need to be atomic (ie all or none), make sure he is using a transaction.

danben 2010-02-07 14:53:28

only 1 writing thread is available which takes place every 4 hours.The writing thread pulls all records from the DB.for each record: it does some task and updates the record with the new received data.so there is a possibility where record i is being updated and the user who owns record i tries to get data from it.so I'll read more about transaction. Is it possible to split the work between separated threads in PHP?I mean the task for each record takes like 10 sec and for performances issues I though to split the work.

embedded 2010-02-07 15:02:28

"so there is a possibility where record i is being updated and the user who owns record i tries to get data from it." - this shouldn't pose you any problem. Assuming you are using a transaction to write the data, the user is either going to see the view of the database before the update or after it, depending on the order in which the queries were received. If the read comes in followed by the write, you can't tell the database to hold off on the read until the write is done, but you shouldn't need to because the user is getting a valid view of the data.

danben 2010-02-07 15:05:47

Re: PHP threads, there are no such thing. You will need to fork multiple processes. See http://www.alternateinterior.com/2007/05/multi-threading-strategies-in-php.html for an example.

danben 2010-02-07 15:08:27

So I should combine all writings to 1 transaction?if the read comes before the write transaction thats fine the user gets the up to date data.on the other hand if the read comes after the write transaction the user could get the data from the DB or the DB is locked until the write transaction is done?Other issue is that the write transaction could take some time and let say the record i was updated but still by using transaction the user won't get the updated data.how do I handle this?

embedded 2010-02-07 15:12:36

You can use transactions and/or locking to make sure that every user sees a consistent view (i.e. completely updated or not updated at all). The database engine itself is very well suited to the scenario. If you are not worried about what readers see at the exact time the updates are running, you don't need to do anything.

calmh 2010-02-07 14:53:54

Considering my scenario I think that no further actions are required. If the write comes before the read the user will get the most updated data otherwise it does not, and he will wait for next query.

This fits my requirements.

Thank you all

embedded 2010-02-07 15:33:09

If any posted answers to this thread helped you reach your conclusion, it is generally polite to upvote and / or accept them. This also helps other readers find useful information.

danben 2010-02-07 16:25:57

You are right, thanks for your help. I have another issue I don't know if it is applicable for mySql.

lets say process A tries to fetch record i. and process B wants to write to record i.

and the scenario is as follows: process A reads only partial data from the record the CPU time goes to process B that completes the write action now process A runs and completes the query so the data process A receives is not complete. (some old and some new)

Is this scenario valid regarding mySql? If so, how do I protect against it? use 2 transactions? 1 for the write and 1 for the read

Thanks

embedded 2010-02-07 16:57:09

"process A reads only partial data from the record the CPU time goes to process B that completes the write action now process A runs and completes the query so the data process A receives is not complete. (some old and some new)"

Fortunately this does not happen with databases. A row is read completely before another thread/process is allowed to modify it (and vice verce).

One problem you will have with your scenario is table locking. While your batch update is running, the table will be locked most of the time and data retrieval will be really slow. A row that is being updated cannot be read.

The snapshot/update/switch approach you mentioned in your original post might be the best approach.

e4c5 2010-02-08 02:09:14

I have 1 row per user.therefore the possibility that a user which owns record i will try to fetch data from record i while other process updates this record is slight.Therefore I think I won't implement any protection for this scenario.but tell me, in the scenario I have mentioned before the user could still read the data before the update took place?Thanks

embedded 2010-02-08 07:28:20

Actually it's more complicated than that when you do a select, the database needs to find out which row to read from (usually by looking at the index, sometimes by a tablescan). If your insert is going to effect the index te select has to wait. If there is no usable index the situation is much worse.

e4c5 2010-02-08 17:51:47

My insert just updates some record's attributes.What do you mean by changing the index?

embedded 2010-02-09 07:34:43

ansaurus

tags:

views:

answers:

concurrent mySql database queries

related questions