questions - transactions and engine

[Leinad]

Hi, I would like to ask you a few question that have me real puzzled

ok

1. what’s the real use of one transaction? and its advantages and disadvantages…

I wonder this because I have a game with multiple users and a lot of updates are going every second…BUT how do I know if a certain transaction went wrong (in this case I should apply ROLLBACK;) or was a sucessful update (COMMIT;)??

2. what’s the main difference and what would you recommend me to use as an engine…innoDB or myISAM?? I know innoDB support transactions but…what’s the wrong side?? what are the advantages and disadvantages, specially for me that I usually don’t know if a valid query is being performed (it should be, as my game in 6 months has no created any problem, but I’m trying to create a new one but more and more things about performance are coming to my head and I don’t know what’s really good)

any help will be really welcomed

thanks in advance )

btw if you would like to check the game I’m talkin’ about if you like to have a better grasp you can find it at vaperida dot com

[sterin]

It is used when it is imperative that a sequence of queries must all be performed or not at all.

The textbook example for a transaction is usually a bank transaction.
Say that you are a bank and that means that you will for example have a table that contains all your clients current account balances.
Now you get an issue from on of your customers to transfer a certain amount of money from his account to his wifes (why on earth he would like to do that is beyond my understanding ).

To solve this in your application you will have to issue two UPDATE statements. One that withdraws the money from his account and one UPDATE that adds the same amount to the wifes account.

Now what happens if something goes wrong between the first and the second UPDATE?
Well without transactions we have managed to withdraw money from the guys account, but they will be lost somewhere in the system because we didn’t manage to issue the new command that places the money on the new account.
While with transactions the first update will automatically be rolled back by the DBMS so none of the UPDATE’s has actually been executed and the money is still safe in the guys account.
Which means that you/he can do a new try at a later time.

So transactions are vital when you need to be sure, that to keep data integrity, several queries should all happen or none at all.

In many applications you don’t have to use transactions and at those times it is overkill to drag around that functionality. Which means that you can choose whatever you want.
InnoDB is a little bit more advanced compared to MyISAM at the same time it has larger storage space and are a bit slower than MyISAM on raw selects.
InnoDB has row level locking while MyISAM has table level locking which means that MyISAM is best suited for applications that mostly reads from the DB.

I usually always go with InnoDB unless you need a fulltext index which only exists on MyISAM tables.