SQLinfo.ru - Все о MySQL

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 12.04.2016 19:06:28

Lioha
Участник
Зарегистрирован: 24.11.2010
Сообщений: 22

multi update одной и той же таблицы.

Здравствуйте!
mysql 5.6.10
MyIsam

Вопрос понимания, как mysql делает update, а точнее где она подготавливает новые значения для полей - во внутренних переменных или непосредственно по указателю на живые данные.
Пример вопроса
мультитабличный update, но таблица одна и та же.

update price p1,price p2 set p1.a=p1.a+3, p2.a=p2.a+1 where p1.id=1 and p2.id=p1.id;

В результате данного запроса поле 'a' таблицы price изменится не на 4, а на 3 или на 1, в зависимости от того в каком прядке запрос обработает поля в set.
Получается, что запись p1.a=p1.a+3 прибавляет 3 не к живым данным в таблице, а к какой то переменной, в которую заранее было помещено значение 'a' из таблицы p1 ? и уже потом эта внутренняя переменная увеличенная на 3 запишется в таблицу?

И основной вопрос, можно ли данный прием использовать как легальный метод получения старых(до update) значений, например:
update price p1,price p2 set p1.a=300, p1.b=p2.a where p1.id=1 and p2.id=p1.id;
т.е. данным update мы в одном запросе и меняем поле и сохраняем его первоначальное значение в другом поле, не боясь того что последовательность выполнения не гарантирована mysql слева на право?

Неактивен

 

#2 13.04.2016 03:33:08

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5842

Re: multi update одной и той же таблицы.

чтобы выполнить ваш milti update сначала требуется найти строки удовлетворяющие условию, т.е. фактически выполнить
select * from price p1,price p2 where p1.id=1 and p2.id=p1.id;
и уже на основе этих данных выполнить update
сначала обновится p1 и в таблицу price будет записано значение исходное+3
потом обновится p2 и в таблицу запишется исходное+1, т.к. p2.a у нас не изменилось.

в этом легко убедиться с помощью триггера, который будет логировать все изменения:


create table test (id int, a int, b int);
insert into test values (1,0,0);
create table test_history (id int, a int, b int);

delimiter //
create trigger test_bu before update on test
for each row
begin
insert into test_history (id, a, b) values(new.id, new.a, new.b);
end//

delimiter ;

MariaDB [test]> select * from test;
+------+------+------+
| id   | a    | b    |
+------+------+------+
|    1 |    0 |    0 |
+------+------+------+
1 row in set (0.03 sec)

MariaDB [test]> update test t1, test t2 set t2.a=t2.a+1,t1.a=t1.a+3 where t1.id=1 and t2.id=t1.id;
Query OK, 2 rows affected (0.17 sec)
Rows matched: 2  Changed: 2  Warnings: 0

MariaDB [test]> select * from test;
+------+------+------+
| id   | a    | b    |
+------+------+------+
|    1 |    1 |    0 |
+------+------+------+
1 row in set (0.00 sec)

MariaDB [test]> select * from test_history;
+------+------+------+
| id   | a    | b    |
+------+------+------+
|    1 |    3 |    0 |
|    1 |    1 |    0 |
+------+------+------+
2 rows in set (0.02 sec)



относительно "меняем поле и сохраняем его первоначальное значение в другом поле", то вы можете воспользоваться для этой задачи single update, в котором порядок вычислений гарантирован:

http://dev.mysql.com/doc/refman/5.7/en/update.html написал:

Single-table UPDATE assignments are generally evaluated from left to right. For multiple-table updates, there is no guarantee that assignments are carried out in any particular order.

Неактивен

 

#3 13.04.2016 12:23:57

Lioha
Участник
Зарегистрирован: 24.11.2010
Сообщений: 22

Re: multi update одной и той же таблицы.

Добрый день!

Да, действительно, тест с триггером очень наглядный, видно что он залогировал оба изменения.
Просто самого понимания не было как делается апдейт, что предварительно выбираются изменяемые поля для каждого вхождения таблицы и только после этого делается изменения исходных ранее считанных значений, а не только что измененных в другом экземпляре таблицы.
Я просто для облегчения примера привел мультиапдейт с одной и той же таблицы, а так конечно в апдейте будет много таблиц и слева на право уже не работает, поэтому и искал варианты гарантированно узнать старое значение до возможного изменения.
Нашел так же способ через пользовательские переменные, с инициализацией в условии

update a=300, b=@var_a where id=1 and least(0,(@var_a:=a))=0

Но про переменные пользователя как то страшно везде описано, вплоть до игнорирования их анализатором в какие то моменты, а про множественный апдейт одной таблицы тоже нигде не написано и может быть пример с триггером не будет работать в других версиях если это конечно не правило работы DB.
Если это правильное поведение DB и в последствии не сломается, то это отличное решение, Спасибо!

Я смотрю в вашем примере с триггером тестировалось на MariaDB, слышал что это параллельное развитие Mysql. Скажите пожалуйста, безболезненно и без переделок можно перенести проект с Mysql на MariaDB? И .net connector и C++ dll все для нее такое же?

Неактивен

 

#4 13.04.2016 16:08:18

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5842

Re: multi update одной и той же таблицы.

прикольное решение smile
с переменными проблемы возникают когда их используют не в правильном месте, например, вычисляют в части where и результат может зависить от того по какому индексу выполняется в данный момент запрос. такой пример хорошо проиллюстрирован в теме http://sqlinfo.ru/forum/viewtopic.php?id=1742
в целом про переменные рекомендую посмотреть Оптимизация запросов MySQL с использованием пользовательских переменных

ваше решение надежно пока single update и условию id=1 соответствует 1 строка.

триггер будет работать однозначно всегда. есть только один недостаток - операция не атомарна, т.е. возможен вариант сбоя (вырубили свет, .. ) когда основной update выполниться а триггер нет или наоборот.

переход на mariadb и обратно должен пройти без проблем, отличия минимальны и зачастую касаются названия службы, коннекторы теже. в доке подробно описано https://mariadb.com/kb/en/mariadb/moving-to-mariadb/

Неактивен

 

#5 13.04.2016 17:13:16

Lioha
Участник
Зарегистрирован: 24.11.2010
Сообщений: 22

Re: multi update одной и той же таблицы.

На самом деле такой update нужен для корректного вычитания баланса покупателя при покупке услуги, а баланс сделан двумя отдельными полями - целая часть(поле a) и копеечная(поле b).
А параллельный update на балансе клиента м.б. как такой же update так и другой, вертающий на баланс сумму.

Допустим нужно вычесть с баланса клиента сумму за услуги в размере 100.99

update client t1, client t2, rules t3, perm t4
set t1.a=t1.a-100-case when t1.b<99 then 1 else 0 end,t2.b=case when t2.b>=99 then t2.b-99 else 100-(99-t2.b) end
where t1.id=1 and t2.id=t1.id and (t1.a*100+t1.b)>=10099 and t3.id=t1.id and (дополнительно проверка по t3) and t4.id=t3.id2 and (дополнительно проверка по t4);

В данном update целая часть баланс клиента меняется в таблице t1, а копеечная часть баланса в t2. Сделано это потому, что если менять целую часть и копейки в t1 а проверять на оригинальные копейки в t2, то получается t2 будет залочена не для записи а только для чтения. И кто знает как тут поступит mysql.

таблицы rules t3, perm t4 - это настроечные и разрешающие по клиенту покупать ту или иную услуги и участвуют только в условии.
во всех таблицах всегда выбирается только по одной строке.

Вот такой update он один раз залочит таблицу client для изменения и оба изменения будут атомарной операцией и между обоими изменением t1 и t2 в этом запросе не проскочит больше ни одного другого update на таблице client?

Отредактированно Lioha (13.04.2016 17:14:15)

Неактивен

 

#6 14.04.2016 19:04:59

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5842

Re: multi update одной и той же таблицы.

Lioha написал:

В данном update целая часть баланс клиента меняется в таблице t1, а копеечная часть баланса в t2. Сделано это потому, что если менять целую часть и копейки в t1 а проверять на оригинальные копейки в t2, то получается t2 будет залочена не для записи а только для чтения. И кто знает как тут поступит mysql.

Нормально поступит.
Лочить t2 и писать в неё никто не будет, т.к. такой таблицы нет. Есть только таблица client, которая и будет заблокирована на запись.
Можете смело менять обе части в t1, используя t2 для проверки первоначальных значений. В этом случае у вас будет 1 update, а не 2 как сейчас.

multiple update не гарантирует порядок присвоения, но, имхо, это относится к случаю, когда меняются данные в разных таблицах. уточните это на bugs.mysql.com и возможно получится упростить запрос, используя таблицу client один раз.

Неактивен

 

#7 14.04.2016 20:35:16

Lioha
Участник
Зарегистрирован: 24.11.2010
Сообщений: 22

Re: multi update одной и той же таблицы.

"...Можете смело менять обе части в t1, используя t2 для проверки первоначальных значений..."
Т.е. если в update имеются два экземпляра одной таблицы, из которых на одну нужно накладывать блокировку записи, а на вторую блокировку чтения, то анализатор(или движек Mysql) наложит на эту таблицу всего лишь один раз и одну блокировку самую строгую - блокировку на запись, а не по порядку - т.е. сначала на чтение, проинициализирует(считает) нужные поля, используемые в запросе, отпустит блокировку и потом наложит блокировку уже на запись?

"...В этом случае у вас будет 1 update, а не 2 как сейчас...."
кстати, а если даже как сейчас(2 раза апдейтится таблица), то даже если обновляемые поля и содержатся в каких то индексах, разве индекс будет два раза обновляться(перестраивается), а не один раз в конечной стадии выполнения всего запроса? Да и сами новые данные вроде тоже один раз фиксироваться будут?
Или это имеется ввиду дополнительная нагрузка только на триггер, если он есть, т.к. он то точно 2 раза отработает в таком update.

"...multiple update не гарантирует порядок присвоения, но, имхо, это относится к случаю, когда меняются данные в разных таблицах...."
Это очень интересно, спасибо, попробуем выяснить. Но мы немного будем модернизировать запрос и там кроме
update client t1, client t2, rules t3, perm t4
появится еще одна табличка новая, в которой тоже будет делаться изменение, все в том же update, но там тоже будет выбираться только одна строка.
Т.е. получается, что multiple update в котором уже не одна таблица изменяема.
Это, кстати, надеюсь, никак не повлияет на "тактику" использовать два раза одну и ту же таблицу для получения старых данных.

И спасибо большое, что отвечаете!

Неактивен

 

#8 15.04.2016 19:18:14

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5842

Re: multi update одной и той же таблицы.

Lioha написал:

Т.е. если в update имеются два экземпляра одной таблицы, из которых на одну нужно накладывать блокировку записи, а на вторую блокировку чтения, то анализатор(или движек Mysql) наложит на эту таблицу всего лишь один раз и одну блокировку самую строгую - блокировку на запись, а не по порядку - т.е. сначала на чтение, проинициализирует(считает) нужные поля, используемые в запросе, отпустит блокировку и потом наложит блокировку уже на запись?

Да, именно так - один раз.

Lioha написал:

кстати, а если даже как сейчас(2 раза апдейтится таблица), то даже если обновляемые поля и содержатся в каких то индексах, разве индекс будет два раза обновляться(перестраивается), а не один раз в конечной стадии выполнения всего запроса? Да и сами новые данные вроде тоже один раз фиксироваться будут?

индекс перестраивается при каждом update, если не указан DISABLE KEYS. Делает и его неявно multi update одной и той же таблицы? Не знаю, но полагаю, что нет.
Фиксация на диск? Это зависит от хранилища. В myisam каждый раз.

Lioha написал:

Т.е. получается, что multiple update в котором уже не одна таблица изменяема.
Это, кстати, надеюсь, никак не повлияет на "тактику" использовать два раза одну и ту же таблицу для получения старых данных.

нет, не повлияет

Lioha написал:

И спасибо большое, что отвечаете!

Да, не за что. Если выясните о порядке присвоения в multi update одной табицы, то отпишите.

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson