Re: [Maria-developers] FOR UPDATE behavior change in 10.3-MDEV-11953
Hi Igor, Please do "reply all" when replying. On 04/12/2018 01:28 AM, Igor Babaev wrote:
Alexander,
If you have the same behaviour you have to use
SELECT * FROM t1 UNION (SELECT * FROM t2 FOR UPDATE).
Thanks. This worked fine for me.
Otherwise it's considered as
(SELECT * FROM t1 UNION SELECT * FROM t2 FOR UPDATE).
What does this syntax mean 10.3-MDEV-11953? Which records should be locked? From t1, from t2, or from both? It seems that it locks nothing. If so, shouldn't this be disallowed? This query (without parentheses) also locks nothing: SELECT * FROM t1 UNION SELECT * FROM t2 FOR UPDATE;
MySQL manual says that 'FOR UPDATE' must follow ORDER BY,
but it does not tell you how to interpret FOR UPDATE in unions.
The old behavior was to interpret UNION parts separately. This locked only t2 in 10.3: SELECT * FROM t1 UNION SELECT * FROM t2 FOR UPDATE; In 10.3-MDEV-11953 it locks nothing. Upgrade looks dangerous. Can we do this way: 1. In 10.4: disallow FOR UPDATE applied to the entire UNION. 2. In 10.3: Add a warning which tells that this feature is deprecated and suggests to use parentheses around union parts when a non-parenthesized union term is used with FOR UPDATE ?
Anyway you can't use anymore: SELECT c FROM t1 ORDER BY a UNION SELECT c FROM t2 ORDER BY b ORDER BY c.
You have to put it so: (SELECT c FROM t1 ORDER BY a) UNION (SELECT c FROM t2 ORDER BY b) ORDER BY c.
(see the standard).
I like this change.
It means that the ORDER BY clause followed a union relates to the union. The same is true for 'FOR UPDATE' because it follows optional ORDER BY/ LIMIT clauses.
Regards, Igor.
On 04/11/2018 09:10 AM, Alexander Barkov wrote:
Hi Sanja, and Igor,
(resending with Igor on CC)
I noticed a FOR UPDATE behavior change in 10.3-MDEV-11953
In 10.3 I run this script in a client session:
CREATE OR REPLACE TABLE t1 (a INT NOT NULL PRIMARY KEY); CREATE OR REPLACE TABLE t2 (a INT NOT NULL PRIMARY KEY); INSERT INTO t1 VALUES (1); INSERT INTO t2 VALUES (2); BEGIN; SELECT * FROM t1 UNION SELECT * FROM t2 FOR UPDATE;
Notice no COMMIT yet! It returns this result: +---+ | a | +---+ | 1 | | 2 | +---+
Now I open a new console, start a new client session and run this script:
BEGIN; SELECT * FROM t2 FOR UPDATE;
The second session gets locked, as expected.
Now I return to the first console and run "COMMIT;". The second console gets unlocked and returns +---+ | a | +---+ | 2 | +---+
Now if I do the same in 10.3-MDEV-11953, the second session does not get locked, it returns the result immediately, even before I typed "COMMIT;" in the first session.
Note, if in 10.3-MDEV-11953 I start a transaction with a simpler query (without UNION) in the first session:
BEGIN; SELECT * FROM t2 FOR UPDATE;
and run the same script in the second session
BEGIN; SELECT * FROM t2 FOR UPDATE;
then the second session gets locked as expected.
Is this behavior change expected?
Thanks!
_______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
Hi, On 04/12/2018 07:59 AM, Alexander Barkov wrote:
Hi Igor,
Please do "reply all" when replying.
On 04/12/2018 01:28 AM, Igor Babaev wrote:
Alexander,
If you have the same behaviour you have to use
SELECT * FROM t1 UNION (SELECT * FROM t2 FOR UPDATE).
Thanks. This worked fine for me.
Otherwise it's considered as
(SELECT * FROM t1 UNION SELECT * FROM t2 FOR UPDATE).
What does this syntax mean 10.3-MDEV-11953?
Which records should be locked? From t1, from t2, or from both?
It seems that it locks nothing. If so, shouldn't this be disallowed?
This query (without parentheses) also locks nothing:
SELECT * FROM t1 UNION SELECT * FROM t2 FOR UPDATE;
MySQL manual says that 'FOR UPDATE' must follow ORDER BY,
but it does not tell you how to interpret FOR UPDATE in unions.
The old behavior was to interpret UNION parts separately. This locked only t2 in 10.3:
SELECT * FROM t1 UNION SELECT * FROM t2 FOR UPDATE;
In 10.3-MDEV-11953 it locks nothing. Upgrade looks dangerous.
Can we do this way:
1. In 10.4: disallow FOR UPDATE applied to the entire UNION.
Or perhaps, is it possible to fix "..UNION..FOR UPDATE" to lock all union parts instead disallowing it and instead of doing nothing (current 10.3-MDEV-11953 behavior)?
2. In 10.3: Add a warning which tells that this feature is deprecated and suggests to use parentheses around union parts when a non-parenthesized union term is used with FOR UPDATE ?
Anyway you can't use anymore: SELECT c FROM t1 ORDER BY a UNION SELECT c FROM t2 ORDER BY b ORDER BY c.
You have to put it so: (SELECT c FROM t1 ORDER BY a) UNION (SELECT c FROM t2 ORDER BY b) ORDER BY c.
(see the standard).
I like this change.
It means that the ORDER BY clause followed a union relates to the union. The same is true for 'FOR UPDATE' because it follows optional ORDER BY/ LIMIT clauses.
Regards, Igor.
On 04/11/2018 09:10 AM, Alexander Barkov wrote:
Hi Sanja, and Igor,
(resending with Igor on CC)
I noticed a FOR UPDATE behavior change in 10.3-MDEV-11953
In 10.3 I run this script in a client session:
CREATE OR REPLACE TABLE t1 (a INT NOT NULL PRIMARY KEY); CREATE OR REPLACE TABLE t2 (a INT NOT NULL PRIMARY KEY); INSERT INTO t1 VALUES (1); INSERT INTO t2 VALUES (2); BEGIN; SELECT * FROM t1 UNION SELECT * FROM t2 FOR UPDATE;
Notice no COMMIT yet! It returns this result: +---+ | a | +---+ | 1 | | 2 | +---+
Now I open a new console, start a new client session and run this script:
BEGIN; SELECT * FROM t2 FOR UPDATE;
The second session gets locked, as expected.
Now I return to the first console and run "COMMIT;". The second console gets unlocked and returns +---+ | a | +---+ | 2 | +---+
Now if I do the same in 10.3-MDEV-11953, the second session does not get locked, it returns the result immediately, even before I typed "COMMIT;" in the first session.
Note, if in 10.3-MDEV-11953 I start a transaction with a simpler query (without UNION) in the first session:
BEGIN; SELECT * FROM t2 FOR UPDATE;
and run the same script in the second session
BEGIN; SELECT * FROM t2 FOR UPDATE;
then the second session gets locked as expected.
Is this behavior change expected?
Thanks!
_______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
This is Muhammad Rashid from Karachi, Pakistan. I need some help to implement MariaDB Master/Master replication with gtid on Microsoft azure environment. Although I have configured the implementation across azure "East US" and "West 2 US" region using vm size "Standard E8s v3 (8 vcpus, 64 GB memory)" through vnet/vnet However I have few points to clear. (1) Can we put Master and slave in scale set. (2) How can improve the latency between regions. (3) Could you suggest me the MariaDB configuration for above machine and 1TB database size having 1 million/hour maximum transaction. (4) How could we avoid single point of failure with in region. (5) Suggest for Microsoft VM size for 1TB database size having 1 million/hour maximum transaction. (6) Segregate the read/write operation without Maxscale. I will be very thankful for your kind support and suggestions. Thanks and regards Muhammad Rashid On Thu, Apr 12, 2018 at 10:11 AM, Alexander Barkov <bar@mariadb.com> wrote:
Hi,
On 04/12/2018 07:59 AM, Alexander Barkov wrote:
Hi Igor,
Please do "reply all" when replying.
On 04/12/2018 01:28 AM, Igor Babaev wrote:
Alexander,
If you have the same behaviour you have to use
SELECT * FROM t1 UNION (SELECT * FROM t2 FOR UPDATE).
Thanks. This worked fine for me.
Otherwise it's considered as
(SELECT * FROM t1 UNION SELECT * FROM t2 FOR UPDATE).
What does this syntax mean 10.3-MDEV-11953?
Which records should be locked? From t1, from t2, or from both?
It seems that it locks nothing. If so, shouldn't this be disallowed?
This query (without parentheses) also locks nothing:
SELECT * FROM t1 UNION SELECT * FROM t2 FOR UPDATE;
MySQL manual says that 'FOR UPDATE' must follow ORDER BY,
but it does not tell you how to interpret FOR UPDATE in unions.
The old behavior was to interpret UNION parts separately. This locked only t2 in 10.3:
SELECT * FROM t1 UNION SELECT * FROM t2 FOR UPDATE;
In 10.3-MDEV-11953 it locks nothing. Upgrade looks dangerous.
Can we do this way:
1. In 10.4: disallow FOR UPDATE applied to the entire UNION.
Or perhaps, is it possible to fix "..UNION..FOR UPDATE" to lock all union parts instead disallowing it and instead of doing nothing (current 10.3-MDEV-11953 behavior)?
2. In 10.3: Add a warning which tells that this feature is deprecated and suggests to use parentheses around union parts when a non-parenthesized union term is used with FOR UPDATE ?
Anyway you can't use anymore: SELECT c FROM t1 ORDER BY a UNION SELECT c FROM t2 ORDER BY b ORDER BY c.
You have to put it so: (SELECT c FROM t1 ORDER BY a) UNION (SELECT c FROM t2 ORDER BY b) ORDER BY c.
(see the standard).
I like this change.
It means that the ORDER BY clause followed a union relates to the union. The same is true for 'FOR UPDATE' because it follows optional ORDER BY/ LIMIT clauses.
Regards, Igor.
On 04/11/2018 09:10 AM, Alexander Barkov wrote:
Hi Sanja, and Igor,
(resending with Igor on CC)
I noticed a FOR UPDATE behavior change in 10.3-MDEV-11953
In 10.3 I run this script in a client session:
CREATE OR REPLACE TABLE t1 (a INT NOT NULL PRIMARY KEY); CREATE OR REPLACE TABLE t2 (a INT NOT NULL PRIMARY KEY); INSERT INTO t1 VALUES (1); INSERT INTO t2 VALUES (2); BEGIN; SELECT * FROM t1 UNION SELECT * FROM t2 FOR UPDATE;
Notice no COMMIT yet! It returns this result: +---+ | a | +---+ | 1 | | 2 | +---+
Now I open a new console, start a new client session and run this
script:
BEGIN; SELECT * FROM t2 FOR UPDATE;
The second session gets locked, as expected.
Now I return to the first console and run "COMMIT;". The second console gets unlocked and returns +---+ | a | +---+ | 2 | +---+
Now if I do the same in 10.3-MDEV-11953, the second session does not
get
locked, it returns the result immediately, even before I typed "COMMIT;" in the first session.
Note, if in 10.3-MDEV-11953 I start a transaction with a simpler query (without UNION) in the first session:
BEGIN; SELECT * FROM t2 FOR UPDATE;
and run the same script in the second session
BEGIN; SELECT * FROM t2 FOR UPDATE;
then the second session gets locked as expected.
Is this behavior change expected?
Thanks!
_______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
_______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
On 04/12/2018 09:11 AM, Alexander Barkov wrote: <cut>
Anyway you can't use anymore: SELECT c FROM t1 ORDER BY a UNION SELECT c FROM t2 ORDER BY b ORDER BY c.
You have to put it so: (SELECT c FROM t1 ORDER BY a) UNION (SELECT c FROM t2 ORDER BY b) ORDER BY c.
(see the standard).
I like this change.
Sorry, I misread the query. This was not actually not possible in 10.3: SELECT c FROM t1 ORDER BY a UNION SELECT c FROM t2 ORDER BY b ORDER BY c;
<cut>
participants (2)
-
Alexander Barkov -
Saad Khan the hacker