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?