Using variables in MySQL instead of a transaction
A classic scenario for a database based application is to query some data, make a decision on it, update the data and write it back. To ensure data integrity you can use a transaction to perform this task.
A simple aproach for your application would be to send several SQL statements (start transaction; select; update; commit;), and wait for the result of each of them before moving to the next. This approach may be easy, but has some disadvantages. During the transaction the database must lock some data and wait for your application. If your application becomes stalled so will your database.
You can solve this problem by using stored procedures. This allows you to put the full logic on the SQL-server. Your application only makes one call. The stored procedure will execute all steps including the final commit, before it sends the data back. this means your DB will not relay on your app to finish the transaction. If your transaction involves a lot of steps, or you have a slower network, this will also save you some time as less data needs to be send over the network. (roundtrip time)
MySQL offers another way to solve this. It works without transactions, which also means you are not bound to use innodb (or falcon), but can also chose myisam.
You can put the whole transaction in a single sql statement. This does mean that you should limit the complexity of decisions made within this query in order to keep the size of such a query reasonable.
You can select and prepare the data using sub-queries inside an "update", "insert on duplicate key update" or mysql's own "replace" to write the data. This covers 3 of the 4 of the steps: read, decision, update. But how do you know what the data was after or before the update? You can not issue another select, as other processes may change the data between your 2 SQL queries.
The answer is MySQL variables. They are scoped to each client connection. Therefore data you store in a variable can not be modified by another process, which has it's own connection to MySQL.
All you need to do is in your insert/update statement is to capture each value of interest and assign it to a variable. You can do that in the subqueries reading the original data or the update part writing the modified data. You can then at any time after the initial query, issue another select to read the variables. All you must make sure is that you use the same connection.
Note: because this relays on the connection scope of MySQL user variables, this may not work with some threaded environments using a connection pool, where each statement will be send over a random connection from the pool. You can still combine this with the stored procedure aproach. This will at least allow you to avoid real transaction and use myisam tables if you wish to.
Example:
update table
user_credits
set
air_miles = (@new_val := (@old_val := air_miles) - ?)
where
user = ? and air_miles > ?;
select @old_val, @new_val;
In this Example the prepared statement takes has 2 references to the value that will be deducted. (In the calculation, and in the where condition)
If you want your prepared statement to only have one reference to the value that will be deducted, you can write it like this:
update table
user_credits,
(select ? as deduction) as t
set
air_miles = (@new_val := (@old_val := air_miles) - t.deduction)
where
user = ? and air_miles > t.deduction;
In both cases you will experience another limit. the Variable is only set, if a row was updated. If the row did not have enough credits, then the variables are not set, and you do not know how many credits the user actually has.
update table
user_credits as u1,
(select ? as deduction) as t
(select @old_val := air_miles, user from user_credits where user = ?) as as u2
set
u1.air_miles = (@new_val := u1.air_miles - t.deduction)
where
u1.user = u2.user and air_miles > t.deduction;
Now you still need to check if a row was updated. No value will be in the variables if the user did not exist. But a value will be found, if the user does exist, but does not have enough miles.
All examples assume user to be the primary or unique key, and that a maximum of one row will be updated.
Back to MySQL