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 <peter_laur...@webyog.com> wrote: > 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 < > ksubraman...@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 >> >> >
_______________________________________________ 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
