[Maria-discuss] AUTO_COMMIT=0 for a session - READ inconsistencyi - ssue or no issue
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?
I think you should refer https://dev.mysql.com/doc/refman/5.7/en/set-transaction.html. It looks like your transaction isolation level is set to REPEATABLE_READ (what is also default in MySQL/MariaDB - unlike in Oracle for instance). If you want to see COMMITS from other sessions in your current transaction it should be READ_COMMITED instead. - Peter - Webyog On Thu, Jun 9, 2016 at 12:46 PM, Karthick Subramanian < ksubramanian@paycommerce.com> wrote:
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?
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Thank you so much. I never expected this much details on Transaction
Controls. I think lot of things need to be unlearned from Oracle. Lets see.
On Thu, Jun 9, 2016 at 4:23 PM, Peter Laursen
I think you should refer https://dev.mysql.com/doc/refman/5.7/en/set-transaction.html.
It looks like your transaction isolation level is set to REPEATABLE_READ (what is also default in MySQL/MariaDB - unlike in Oracle for instance). If you want to see COMMITS from other sessions in your current transaction it should be READ_COMMITED instead.
- Peter - Webyog
On Thu, Jun 9, 2016 at 12:46 PM, Karthick Subramanian < ksubramanian@paycommerce.com> wrote:
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?
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
participants (2)
-
Karthick Subramanian -
Peter Laursen