MorkaLork Development

Interesting stuff I've picked up over the years...

Mysql 5: Safety handling

2009-04-16 18:08:30 | 295 views | transaction safe safety working tables acid a.c.i.d. sql server mysql atomicity consistency isolation durability start transaction commit backup

Working with transactions

Sometimes when you're working with a database and alter tables in any way(inserting, updating etc) you might want to make sure that all commands entered in a query are executed. One good example is if you have a system that works with accounts containing money. If you move money from one account from another, all you really to is tell one account to withdraw a certain amount, and another to add that same amount. What if you first enter a query to withdraw money from one account, and then, before you enter the query to add it to the other account, the whole system crash. Well, then that amount of money is lost.

What you need to do is make sure that both queries are run, no matter what. This is where transactions comes into play. You tell the database that only run both queries; if one fails, stop the transaction and reset the database.

This article will only cover the MySql way. If you're using SQL Server you might find THIS article helpful.

MySql doesn't have the same support for Transactions that SQL Server has (for some raisin), but most things can be done anyways.

What does A.C.I.D. mean?

A.C.I.D. stands for Atomicity, Consistency, Isolation and Durability.

Atomicity: Means that if your transaction involves two or more action, either all of them will be executed or none.
Constistency: Means that if all queries are run, you get new data, but if something fails, everything is returned to state it was before.
Isolation: All transactions are kept separate from eachother while in process.
Duability: Means that the data is saved by the system even if the system crashes or reboots.

Atomicity means that you wrap multiple SQL statements together as one single entity. This also means, that if one part fails, everything fails.
Isolation means that only one transaction can be run at a time. If two transactions are called at the same time, one will go before the other to prevent interferance. However, the isolation property does NOT say what transaction will go first, just that one will go before the other.
When executing the commands, the consistency of the database as it was before cannot be broken by the transaction. All tables and their relationships(such as foreign keys) must be as consistant before as they are after the transaction.
The durability part means that the database will keep track of whatever changes are being made so that if the the system crashes in anyway, the database must be able to recover to it's original state. If the transaction is successfull however, the changes are final and cannot be reversed.

So how is it used?

Let's first create a table of accounts.

name varchar(255) NOT NULL,
money int

And then it will look like this(after entering some records):

|ID |name |money |
|1 Henry Blake 500 |
|2 Radar OReilly 150 |
|3 Hawkeye Pierce 900 |

Now, last night, Henry Blake lost some money to Hawkeye Pierce in a poker game. Henry must make a transaction from his account to Hawkeyes in order to keep it fair. Now this is how it could be done:

UPDATE accounts SET money=money-200 WHERE ID=1
UPDATE accounts SET money=money+200 WHERE ID=3

However, if the transaction would for some reason fail after the first query, Henry Blake would be out 200 moneys, but Hawkeye wouldn't have recieved them. The money would be gone in cyberspace. We need to ensure that both these queries are run, or none of them.

UPDATE accounts SET money=money-200 WHERE ID=1;
UPDATE accounts SET money=money+200 WHERE ID=3;

Here is where ACID comes into play. We expect the queries to be treated as one action. If one part fails, everything fails(Atomicity). When we run the data, if the first fails and the money is withdrawn, and the transaction fails, everything will be reset to the databases original state(consistency). We are also sure that if someone else uses these tables, the queries won't be nested. That might have messed alot of things up. What if we withdrew 200 from Henry Blake while someone else was getting a read on the account, they would get the new sum(-200), but then our query fails and we reset the value, they would still have a faulty image of the table. That won't happen since the transaction promises that our query will run alone when it runs(either before or after another simultanious query)(isolation).
Finally, we can rest assure that whatever happens, there is a backup "image" of the original table state so that if anything goes wrong, it can always be restored(durability).

To sum it up...

Safety and integrity of the database is a vital part of the process of altering a table. If you're just creating a home application on your local SQL server this might not feel that important, but you have to consider the fact that you might some day work on a multi-user database. If that happens and security is not considered, bad things can happen, data can dissapear and alot of work can be lost.

Using these methods can easily ensure that everything you do is handled with care and does not %¤#(! up the integrity of the database.
There is alot more to it than just this, obviously, but this is more an article about the concept than the specifics. More detailed articles will come up soon.
Hope you found this article interesting and helpful!

Article comments

Feel free to comment this article using a facebook profile.

I'm using facebook accounts for identification since even akismet couldn't handle all the spam I receive every day.