maria-discuss team mailing list archive

[Peter Laursen <peter_laursen@xxxxxxxxxx>]

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@xxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp
>
>