maria-discuss team mailing list archive

[Karthick Subramanian <ksubramanian@xxxxxxxxxxxxxxx>]

Observation:

If we set the AUTOCOMMIT=0 in a session, then in that session, I noticed a
data inconsistency. Whatever data committed in other sessions, when we
select in this session showing until we start any new transaction in this
session. The moement we sstart any new transaction in this session, then
its not showing any latest data for a table (data inserted by other
sessions) in this session, unless we explicitly issue the commit. Usually
AUTOCOMMIT=0 means any specific transactions in that session will be
visible to other sessions only when we explicitly issue a COMMIT, but we
can read a consistent data from that session. It doesn't seem to be
working.

Is this my misunderstanding on how a Transaction management works or a bug?

Session 1:

MariaDB [employees]> select * from tab;
Empty set (0.00 sec)

MariaDB [employees]>   INSERT INTO tab
    -> (c1, c2)
    -> VALUES
    -> ('a1', 'a11');
Query OK, 1 row affected, 1 warning (0.00 sec)

MariaDB [employees]> select * from tab;
+------+------+
| c1   | c2   |
+------+------+
| a1   | a1   |
+------+------+
1 row in set (0.00 sec)

MariaDB [employees]>

Session 2:

MariaDB [employees]> select * from tab;
+------+------+
| c1   | c2   |
+------+------+
| a1   | a1   |
+------+------+
1 row in set (0.00 sec)

MariaDB [employees]>   INSERT INTO tab
    -> (c1, c2)
    -> VALUES
    -> ('a2', 'a22');
Query OK, 1 row affected, 1 warning (0.01 sec)

MariaDB [employees]> select * from tab;
+------+------+
| c1   | c2   |
+------+------+
| a1   | a1   |
| a2   | a2   |
+------+------+
2 rows in set (0.00 sec)

back to Session 1:

MariaDB [employees]> select * from tab;
+------+------+
| c1   | c2   |
+------+------+
| a1   | a1   |
| a2   | a2   |
+------+------+
2 rows in set (0.00 sec)

Back to session 2:

MariaDB [employees]> set auto_commit=0;
ERROR 1193 (HY000): Unknown system variable 'auto_commit'
MariaDB [employees]> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

MariaDB [employees]>

Move to Session 3:

MariaDB [employees]> select * from tab;
+------+------+
| c1   | c2   |
+------+------+
| a1   | a1   |
| a2   | a2   |
+------+------+
2 rows in set (0.00 sec)

MariaDB [employees]>

Move to session 1:

MariaDB [employees]>   INSERT INTO tab
    -> (c1, c2)
    -> VALUES
    -> ('a3', 'a33');
Query OK, 1 row affected, 1 warning (0.00 sec)

MariaDB [employees]> select * from tab;
+------+------+
| c1   | c2   |
+------+------+
| a1   | a1   |
| a2   | a2   |
| a3   | a3   |
+------+------+
3 rows in set (0.01 sec)

Move to session 2:

MariaDB [employees]> select * from tab;
+------+------+
| c1   | c2   |
+------+------+
| a1   | a1   |
| a2   | a2   |
| a3   | a3   |
+------+------+
3 rows in set (0.00 sec)

Move to Session 3:

MariaDB [employees]> select * from tab;
+------+------+
| c1   | c2   |
+------+------+
| a1   | a1   |
| a2   | a2   |
| a3   | a3   |
+------+------+
3 rows in set (0.00 sec)

Across all 3 sessions, the data is consistent.

Move to session 1:

MariaDB [employees]>   INSERT INTO tab
    -> (c1, c2)
    -> VALUES
    -> ('a4', 'a44');
Query OK, 1 row affected, 1 warning (0.00 sec)

MariaDB [employees]>  select * from tab;
+------+------+
| c1   | c2   |
+------+------+
| a1   | a1   |
| a2   | a2   |
| a3   | a3   |
| a4   | a4   |
+------+------+
4 rows in set (0.00 sec)

Move to Session 2:

MariaDB [employees]>   INSERT INTO tab
    -> (c1, c2)
    -> VALUES
    -> ('a5', 'a55');
Query OK, 1 row affected, 1 warning (0.01 sec)

MariaDB [employees]>   INSERT INTO tab
    -> (c1, c2)
    -> VALUES
    -> ('a6', 'a66');
Query OK, 1 row affected, 1 warning (0.00 sec)

MariaDB [employees]> select * from tab;
+------+------+
| c1   | c2   |
+------+------+
| a1   | a1   |
| a2   | a2   |
| a3   | a3   |
| a5   | a5   |
| a6   | a6   |
+------+------+
5 rows in set (0.00 sec)

a4 record is missing here in session 2 - inconsistency started.

move to session 3:

MariaDB [employees]> select * from tab;
+------+------+
| c1   | c2   |
+------+------+
| a1   | a1   |
| a2   | a2   |
| a3   | a3   |
| a4   | a4   |
+------+------+
4 rows in set (0.00 sec)

In Session 3 - a5 and a6 records are mising because in session 2 -
autocommit=0, so its expected that a5 and a6 are missing in session1,3.

BUT, why in session 2, the a4 record is missing?