multi_query

[chiprivers]

In my database, I have a table which contains 'status' records.  Each new entry made to the table may superceed one or more of the previous entries in the table.  Any records which are being superceeded need to be updated with the colum 'recordStatus' being set to 0.  All new records will have a 'recordStatus' default to 1.

 

I have recently moved to mysqli for my mysql database interaction and I have been using prepared statements.  The current function I have produced to insert new records into the above table, and also update any existing entries at the same time, is using prepared statements and executing the relevant insert or update statement for each query as required.

 

Elsewhere in the script, this same status table will be queryied to return all records where the recordStatus value is 1.  My concern is that this SELECT query may return spurious results if either:

i) the select query is processed in between the insert and update queries in the above function, at which point there may be multiple conflicting status records with their recordStatus value still set to 1

or ii) if there is an error in processing the above function so that all of the required update or insert statements are not completed successfully.

 

First of all, is this an unneccessary concern?

 

Or should I use the multi_query function to submit all required queries at once, which I believe will lock the table until all necessary updates are complete?

 

I have not used the multi_query function before so could someone give me some quick guidance on this? can I still used prepared statements with the multi-query or should I use some other checks on any input values to ensure that there is no SQL injection?

[mikosiko]

without seen your code seems unnecessary to me... you only have to implement a good error control and proceed accordingly if you detect any error.

 

in any case here are examples of multi query usages

http://php.net/manual/en/mysqli.multi-query.php

 

and yes you can continue using prepared statements

[chiprivers]

OK, errors aside, what if another user is viewing a page which calls a SELECT query on this table right in the middle of my script which is inserting a new record aswell as updating any existing conflicting entries.

 

I cant show you the code as I have not completed it yet but to give you an idea of what I am doing...

 

Each 'status' entry will have a 'start' and 'end' time and a 'statusCode'.  If the time period of the new entry overlaps any time period of any existing records, the existing records need to be updated.

 

What I intend to do is first query the database to return any records where there is an overlap in the time periods and where the recordStatus is set to 1.  I will loop through each of these returned records and see where the overlap is, creating further new entries for any period at either the start or end of the new period.  All returned records will then be updated with recordStatus = 0.

 

Then the new record will be created.

 

This should explain further my concern that if the table is queried at any time during the above list of operations, it will not give the desired result.  It could return multiple overlapping time periods or the new record may not be created but the old ones set to 0 so no records would be returned.

 

I appreciate that it would have to be pretty unlucky but as this will be a mutli-user application with potentially several updates to this particular table being made at any one time, whilst also other multiple users reading the data from this table, I would like to make sure the coding is appropriate from the start.

[mikosiko]

what if another user is viewing a page which calls a SELECT query on this table right in the middle of my script which is inserting a new record aswell as updating any existing conflicting entries.

 

I'm sure that you realize that a SELECT and further display of the results are merely a snapshot/picture of the status of what has been selected in that specific instant  ... and that snapshot could be absolutely invalid a millisecond (for quantify in some way) after it was taken in a multi-user environment right?...

 

that scenario can be managed in part with help of atomic operations (updates in particular) which implement automatic locks (default in MYISAM engine), or TRANSACTIONS and different (table/rows) locking mechanisms in addition to the standard internal lockings provided by MYSQL.

 

if you want to know more about:

http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-transactions.html

http://dev.mysql.com/doc/refman/5.0/en/locking-issues.html

 

and this, even when is kind of old contain some basic and interesting examples that could help you

http://www.databasejournal.com/features/mysql/article.php/3382171/Transactions-in-MySQL.htm

[chiprivers]

I appreciate that any select query gives a snapshot in time and this could be out of date in a multi user enviroment, however, if a snapshot is taken without the full set of updates I talk about being completed, it will not even be a valid snapshot.  The displaying page will also be reliant on calculations based on the data taken from the returned rows and if the full set of updates are not complete, it will mess up the calculations.

 

Im sorry if I am not explaining this very well.