subject:"\[Maria\-discuss\] AUTO_COMMIT=0 for a session \- READ inconsistencyi \- ssue or no issue"

Re: [Maria-discuss] AUTO_COMMIT=0 for a session - READ inconsistencyi - ssue or no issue

2016-06-09 Thread Karthick Subramanian

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 
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

Re: [Maria-discuss] AUTO_COMMIT=0 for a session - READ inconsistencyi - ssue or no issue

2016-06-09 Thread 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 <
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 -
>

[Maria-discuss] AUTO_COMMIT=0 for a session - READ inconsistencyi - ssue or no issue

2016-06-09 Thread Karthick Subramanian

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

Re: [Maria-discuss] AUTO_COMMIT=0 for a session - READ inconsistencyi - ssue or no issue

Re: [Maria-discuss] AUTO_COMMIT=0 for a session - READ inconsistencyi - ssue or no issue

[Maria-discuss] AUTO_COMMIT=0 for a session - READ inconsistencyi - ssue or no issue

3 matches

Site Navigation

Mail list logo

Footer information