Life Doesn't Have to Be Logical. We Fall in Love, Get Attached To Someone, Promise Ever-Lasting Love, And The Worst Part Irs of Course The Breaking Up.
.. It's the same with databases We decide we like this database, so we store records into it We promise ourselves that the database will hold our records for ever and ever, and then the database crashes; now we want to strangle the database server. .
This is where transactions are better real-life love than. Transactions is a technology that ensure that if you have to update multiple tables and you crash midway, you can rollback the data to a consistent state just before the crash. Imagine doing that with your Loved One!
Mysql is The Most Popular Open Source Database On Earth. The Current Stable Release, 3.22 Does Not Support Transactions, But With A Little Bit of Intelligence and Discipline, We can Simulate Transactions.
How Transactions Work
An Example of How We Use Transactions Is A Shopping Cart System After Checkout. Here We Are Generating An Invoice and The Invoice Items Based on The Contents of a shopPing_cart_Items Table.
Suppose we crash after creating the invoice record, but before all the invoice items are created Or suppose we crash before we can delete all shopping cart items Then we will be double-counting the items:.. Once in the shopping cart, the other in The invoice.
Transactions Help Solve The Problem, AS CAN Be Seen Below in Pseudo-Code:
Begin Tran;
INSERT INTO Invoice (...) VALUES;
$ PARENT_ID = mysql_inserted_id ();
For Each Shopping_cart_Items
INSERT INTO INVITEMS (..., Invoice, ..) VALUES (..., $ PARENT_ID, ...);
DELETE AUPPING_CART_ITEMS WHERE CART_ID =?
COMMIT TRAN;
Now if we crash before we insert all the invoice items, the database will notice that a transaction was taking place, and will rollback the data to the state it was before the begin tran. So the invoice is not generated because the commit tran was never Executed, and the shopping cart remains intact.mysql
The current stable version of MySQL (3.22) does not support transactions. This is a feature currently in testing for 3.23, but I forsee that many web hosts will not be upgrading for some time to come. Not to worry, we'll simulate them USING SOME TECHNIQUES Developed Long Ago for Older Databases.
THEN Are 2 Types of Transactions We can Simulate, Record Creation Transactions
Record Creation Transactions
In the Above Example, We created the invoice record first..................... ..
In Simulate Transactions, We do Things the Other Way Round. The Child Records Must Be CREATED FIRST, THE PARENT.
Why? Because normally we view information from top-down, parent to child. In a simulated transaction, child records are treated as invalid if the parent record has not been created yet. To do this we also need to generate synthetic keys in advance as Primary Keys for the Invoice TABLE.
For example, assuming we have a function called generate_key () to generate the synthetic keys:
$ PARENT_ID = generate_key ();
For Each Shopping_cart_Items
INSERT INTO INVITEMS (..., Invoice, ..) VALUES (..., $ PARENT_ID, ...);
INSERT INTO Invoice (ID, ...) VALUES ($ PARENT_ID, ...);
In this case, if we crash while updating the invitems, the invoice record is not created. Provided we never access the invitems records without joining to the parent table, we are ok. The simulated transaction will work.To be safe, we can run A Batch Job in Background To Delete Orphaned Child Records.
Now you are probably saying, We Didn't Cover The Delete. What happens? The delete? Then we would last shopper and being shipped to the me
Update / Delete Transactions
This Sort of Transaction is Harden To Handle. We need to import a status field what tells us WHETER We Are Outside or Inside a transaction.
THE Any Transaction Occured WHENE CRASHER Any Transaction Occured WE CRASHED, AND Perform A Repair if IT IS SO.
For Our Example:
Update shopping_cart_items set status = 'in_trans' Where ID =?;
$ PARENT_ID = generate_key ();
For Each Shopping_cart_Items
INSERT INTO INVITEMS (..., Invoice, CartId, ..)
VALUES (..., $ PARENT_ID, $ CARTID, ...);
INSERT INTO Invoice (ID, ...) VALUES ($ PARENT_ID, ...);
DELETE AUPPING_CART_ITEMS WHERE ID =?;
THEN IN YOUR PSEUDO-CODE:
SELECT CARTID, ID from shop online_cart_items, invitess
Where status = 'in_trans'
And infitems.cartid = shopping_cart_items.cartid
INTO $ CARTID, $ ID;
For Each ($ CARTID, $ ID)
Select ID from invoice where infoice.id = ID;
IF no records returned
Delete from invitems where cartid = $ cartId;
Else
Delete from shopping_cart_items where cartid = $ cartID;
The Same Method of Repair Used for deletes is also used for updates.
If your MySQL database is out-sourced, and you are not informed when the MySQL database is restarted, then you need to do periodic scans and repairs. Sounds a bit like fsck or scandisk does not it? In MySQL, we can generate the .
For Example, Assuming We create a Table Called Invoiceid With One Record Containing One Field Called ID. In Pseudo-Code:
Function generate_key ()
{
Lock Tables Invoice Id Write;
Update InvoiceID Set ID = ID 1;
Select ID from InvoiceID INTO $ ID;
UNLOCK TABLES;
Return $ ID;
}
Conclusion
So all you broken-hearted lovers out there -. Get jealous Transactional databases do it better They can rollback to the starry-eyed time when lovers are still in love And MySQL can do it too with a little bit of love and care.. .
FEEDBACK
Since this article was released, I have received some feedback: mostly concerns about the risks involved in using MySQL Well, I have actually used these techniques and they work, but I also agree that they are not a complete substitute for a database system that. Fully Supports Transactions.
You need to think about the risks involved in using these techniques with MySQL or similar databases. If you do not feel comfortable, I suggest you invest some money in getting a Relation Database Management System that supports transactions such as Interbase, Oracle or MSSQL 7 .
You will still need to code with discipline even if you are using Oracle. It is possible to write buggy transaction code that will corrupt the data integrity on rollback. There's no substitute for good code.Best wishes, and let's hope we never have to rollback Our love-life.
Read / Post Responses (Join / Login First)
