what transaction is in MySQL?

Still i didn't get what transaction is in MySQL? What is advantage of it and how can i apply?

Simple explanatio开发者_StackOverflown please, but not so simple. I know what DB is..

---

Transactions help when you have several related SQL statements that must either succeed or fail all together as a single unit of work.

The classic example is a transfer of funds between two bank accounts. You don't want the withdrawal to succeed and the deposit to fail; your bank doesn't want the deposit to succeed unless the withdrawal does, too.

A transaction monitors all SQL executed within the transaction to ensure that all succeed. If anything fails, it rolls back all the statements.

---

A transaction is a set of changes that aren't comitted (in other words, saved) until you run the commit command. So transactions allow you to say "I want to make all these changes to the db, but don't actually save my changes until the entire list is done." If there's an issue along the way, you can rollback (in other words, undo) any changes you were in the process of making.

---

A transaction (not only in MySQL) is way some code is executed. However if something goes wrong than the transaction will be reversed to no changes are made if the full process is not completed.

Example:

Transaction begins:
doA();
doB();
doC();
Transaction ends:
doD();

If A fails, nothing changed.
If B fails, nothing changed.
If C fails, nothing changed.
If D fails, A,B,C are still executed.

If something fails inside the transaction a so called rollback is done which reverses all changes that are made.

---

Transactions are not limited to MySql - most RDBMS use them.

They are useful when you are doing several related operations and need them to behave atomically - that is, either all of them happen or none.

For example - you want to save an Order and all of the OrderItems. This is best done in a transaction, as one doesn't mean much without the other.

The wikipedia article is quite informative.

Transactions provide an "all-or-nothing" proposition, stating that each work-unit performed in a database must either complete in its entirety or have no effect whatsoever. Further, the system must isolate each transaction from other transactions, results must conform to existing constraints in the database, and transactions that complete successfully must get written to durable storage.

---

The wikipedia page does this justice. Basically however a transaction allows you to perform statements as one action, in isolation. It also allows you to undo mistakes. I.e.

START TRANSACTION;
DELETE FROM foo WHERE bar = baz;
ROLLBACK;

Will have no changes.

Transactions are also isolated. This means that the view the first statement sees is the same as the last one. If we do

SELECT * FROM foo;
SELECT * FROM foo;

outside a transaction the results will be different if someone has altered the table in the meantime. In a transaction however both produce the same result - the view of the table when the transaction was started.

---

Still i didn't get what transaction is in MySQL?

BEGIN TRANSACTION

INSERT
INTO    mytable
VALUES  (1)

INSERT
INTO    mytable
VALUES  (2)

COMMIT

As a result of this, you either get both values (1 and 2) in the table, or get none.

There is no chance that one value gets there and the other one does not.

---

Here is some information:

http://en.wikipedia.org/wiki/Database_transaction

Basically, a (database) transaction is a way to group update statements. If something goes wrong with one statement, you can "undo" (rollback) all statements, so the information in the database remains "correct".

A simple example would be if you insert a person and their address into the database. If you insert the address first and then the person, and there is an error in the person info, you don't want the address to be in the database without the person whose address it is. If you used a transaction, you can undo all operations at once.

I hope this makes some sense :)

---

When you perform an insert or update on a database, the update/insert takes affect immediately. When you have a transactional table the update/insert is queued until you have queued up all the actions that need to be carried out. At this point you tell MySQL to commit all of the changes at the same time. The benefit of this is now, if any of the actions fail none will take affect.