Jump to content

Are Cubecart 5 database updated transactional?


Recommended Posts

Does that mean it will some time in the future? The potential for database inconsistencies makes me nervous...

Really don't know. Unsure even it brings more possitives than negatives. Tables are beiing locked by any insert/update/delete and transaction is commited immediately by default. What kind of inconsistence are you thinking about?

Link to comment
Share on other sites

When you have time, would someone please give a simple explanation of what a transactional update is? Just interested in learning, so no hurry on this one.

Pretty basic example. Using transactions you can:

- start a transaction

- do the first query

- if first query is successful do the second query.

If second query succeeds then commit the transaction, else rollback the transaction (cancell both queries and results that correspond to that transaction).

Link to comment
Share on other sites

A database transaction is all of the updates required to complete a business transaction. In our case, a sale debits a gift certificate, saves one order header and hopefully many items and decreases several stock records, so a transaction may update many tables, some of them several times. If something happens to interrupt this, then there is the potential for errors to appear in the database.

The purpose of a transactional update is to ensure that either all of a transaction is recorded, or none of it. According to the eloquently named Codd and Date who laid out the theory of relational databases in the 1970's, a transaction should pass the ACID test:

  • Atomic - so the entire transaction is recorded or nothing is recorded
  • Consistent - is complicated, and it is tricky to implement - it means that someone who starts running a long-running report sees the data as if it was a snapshot, even though it might take them a day to scan the data, and updates are being done all the time the report is running
  • Isolation - means that transactions don't interfere with each other as they are running.
  • Durability - means that once the data is committed, only strategic incompetence can lose it. In particular, a simple machine crash with restore can recover up to the last committed transaction, and roll back the saved bits of unfinished transactions.

The big one for CubeCart is atomicity. When we make a sale, we:

  • optionally update a gift certificate,
  • insert an order row in the order_sum table,
  • insert the items in the order_inv table
  • update the stock for (hopefully) multiple items in the inventory table.

There is a risk that a coding error, machine crash, DOS attack or some other nastiness intervenes between the stock update and the order save (doesn't matter which comes first.) These two are balancing updates. If this process is interrupted, then the stock-in-store will be wrong and you probably won't know until you get an order for something you no longer have in stock.

Durability is another important thing for us - As I understand it, the MySQL ISAM does not maintain a redo log, so if the machine crashes, there is no way for it to clean up so when it restarts you get whatever bits of transactions made it onto the disk before the disruption. The logical grouping of related updates is not recorded in the redo logs, so there is no information describing how to clean up.

We really don't know what kind of hardware our ISPs are using. I sold a three year old Dell server once to a guy who was setting up a server farm in his garage. I am pretty sure that none of his $5/month online store customers were going to ask for a site inspection. Unless you are sitting there playing with your store all day, you really do not know how many times it goes away. If we get the occasional order, we assume it is 100% available but we are buying a service from an operator we have never seen in a very competitive environment... I think some update integrity would be useful.

On the whole, this is not a big deal. I run my store on CubeCart and I love it. I particularly love that I can get in there and adapt it. (Although I am less in love with this idea now that I have to upgrade to CC5...) However transactional updates would make a good product better.

Convict said...

"Unsure even it brings more possitives than negatives."

One negative might be a higher database load - I imagine that innoDB needs more iron than ISAM - but that is provided by someone else, right? And you have to do some recoding. I have not had a look at the reorganization that when with the PHP Objects conversion but I would expect that this would make the switch to be somewhat easier.

There are other benefits too - MyISAM does table-level locking so for busy stores there should be a huge performance improvement. I look forward to the day when this becomes a problem for me.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...