Can some one explain me "TRANSACTION" and "transaction isolation levels" with good example. I am very much confused for using this within my application. I am doing many Insert/Update/Select transaction within Stored Procedure, so please explain in this context, (consider auto-commit too). I am using connection pooling too on my application server.
Thanks.
These are different concepts that all play well together. Transactions are one very basic and important concept in databases that i use every day. You can read quite a bit about the most important properties of transactions, ACID, here: http://en.wikipedia.org/wiki/ACID
But I'll try to give you an overview with my own words:
Transactions can be seen as grouping together a set of commands. If you change/add/delete anything in the database within a transaction, depending on the isolation-level noone outside that transaction can see these changes. And if you rollback the transaction (for example if an error occurs) there are no changes applied to the database at all. If you otherwise decide to commit your transaction, everything that happend within the transaction is executed at once. So as a good habit, grouping every logical action together in one transaction is a brilliant idea.
Auto-commit is the opposite: Every update/insert/delete is implicitly/directly commited as transaction. So it can still be seen as a transaction, but you omit the explicit commit at the end of it.
Connection pooling can only work if you ensure to use only one connection for a transaction. But usually you have to first get a connection from the pool to execute your statements, so this is no issue.
Prepared statements are a bit unconnected to transactions. You can of course use transactions within prepared statements and have to consider that because nested transactions are not possible in MySQL.
