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
