Mysql transaction : commit and rollback

Question

I updated my PhpMyAdmin database engine from MyISAM to INNODB to allow rollback.

This is my SQL query :

START TRANSACTION;
UPDATE jkm_content SET state=0 WHERE title IN ('title-1','title2');

And the result :

start transaction;# MySQL returned an empty result set (i.e. zero
rows).
UPDATE jkm_content SET state=1 WHERE title IN ('title-1','title2');# 2 rows affected.

1) So the statement informs me that 2 rows are affected but the change doesn't appear anywhere (neither in my DB nor in the website).I though start transaction would allow me to visualize the changes (in a temporary DB) and then if I am satisfy I "commit" the query. (I understand I need to commit to update the DB, but if I commit the change will be permanent).

2) Then I don't get the point of rollbackif I can't see the effect before committing it. What will be the difference between these two queries :

START TRANSACTION;
UPDATE jkm_content SET state=0 WHERE title IN ('title-1','title2');

AND

START TRANSACTION;
UPDATE jkm_content SET state=0 WHERE title IN ('title-1','title2');
ROLLBACK;

3) if I got it right, these functions are all the same :

START TRANSACTION
BEGIN
BEGIN WORK

Solution 1

1) All changes you make are visible within the same transaction. If you do

START TRANSACTION;
INSERT INTO MyTable VALUES ('Hi there');
SELECT * FROM MyTable;

your output will include the 'Hi there'. But if you start a second database-connection the new row won't be displayed until you commit your transaction from within the first connection. Try playing with this using two database-connections using the command-line.

You're not seeing the effect in your website because you can't have the same transaction within two database-connection (a new db-connection will be made at the beginning of your request).

2) All transactions that aren't committed will be rolled back when the connection with the database is closed. So if these are your only two queries, there are no difference. However there is a difference between

START TRANSACTION;
INSERT INTO MyTable VALUES ('This one would be discarded on rollback');
ROLLBACK;
INSERT INTO MyTable VALUES ('This one will be permanent because not within transaction');  

3) Yes, these are all the same.

Solution 2

1. Changes you made within one transaction are not visible to other transactions (except transactions with READ UNCOMMITTED isolation level) until the transaction is committed.

2. There is a huge difference between rolling back transaction and keeping it open forever (or until the engine kill it due to timeout). The latter means server cannot free resources allocated to support transaction. In addition, since you do UPDATE, mysql has to issue exclusive locks on rows affected, and no other transaction can update/delete these rows. If you have an application that leaves transactions open, you will very likely end up with either all connections busy and waiting forever, or bunch of deadlocks .

3. Yes, they all start a new transaction in mysql.