← Back to team overview

maria-discuss team mailing list archive

Re: AUTO_COMMIT=0 for a session - READ inconsistencyi - ssue or no issue

 

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_laursen@xxxxxxxxxx>
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 <
> 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
>>
>>
>

References