CODEWITHSUNDEEP
mysqltransactionscommit
Suhail Gupta
Suhail Gupta

Reputation: 23266

Why does the record gets inserted when commit was not issued?

On running the following statements:

set autocommit = false;
start transaction;
insert into test_table (id,name) values('6','iqbal bano');

The entry gets inserted into the database, although I didn't issue the command commit. Is this behavior natural to mysql? If yes, why is it so? To my surprise, if I run 3 statements as:

set autocommit = false;
start transaction;
insert into test_table (id,name) values('7','chitra singh');
insert into test_table (id,name) values('8',nayaara);

The third statement, returns an error (because nayaara had to wrapped inside the quotes) but, the previous insert succeeds and entry with id 7 is created into the database. What is the reason for this? Is this the way the transaction will proceed?

Note: Autocommit is set to false, as shown in the above statements.

Upvotes: 1

Views: 708

Answers (1)

Razor Tag
Razor Tag

Reputation: 36

If you set the auto-commit to false and the inserts go through, its part of the transaction but doesn't get committed to the database. If you were to restart the database the inserts would be lost; the transaction is rolledback since you did not say commit. However if you commit and then restart the records are not lost.

mysql> set autocommit=false;

    Query OK, 0 rows affected (0.00 sec)

    mysql> insert into test (id) values (5),(6),(7),(8);
    Query OK, 4 rows affected (0.00 sec)
    Records: 4  Duplicates: 0  Warnings: 0

    mysql> select * from test;
    +------+
    | id   |
    +------+
    |    1 |
    |    2 |
    |    3 |
    |    4 |
    |    5 |
    |    6 |
    |    7 |
    |    8 |
    +------+
    8 rows in set (0.00 sec)

    mysql> exit
    Bye
mysql> select * from test;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+
4 rows in set (0.00 sec)

mysql> insert into test (id) values (5),(6),(7),(8);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from test;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
+------+
8 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
+------+
8 rows in set (0.00 sec)

Upvotes: 1

Related Questions