Меню
Что нового?

Блокировка внутри транзакции

Статус
В этой теме нельзя размещать новые ответы.

GrayMaster

Новичок
Блокировка внутри транзакции

Привет!
При отладке скриптов заметил следующее. Если блокировать таблицу внутри транзакции, то транзакция "срывается". Т.е. если будет ошибка после блокировки, то изменения в таблицах останутся, а не отменятся.
Это нормально? Или глючит MySQL сервер?

Примеры:
1.
start transaction;
insert into `a`...;
update `a`...;
die('stop');
commit;

тут у нас перед коммитом скрипт дохнет, и все изменения сделанные в сесии отменяются.

2.
start transaction;
lock tables `a` write;
insert into `a`...;
update `a`...;
unlock tables;
die('stop');
commit;

тут же, эфект такой, как буд то блокировки нет.

В общем вопрос таков: это специфика SQL запросов, или глюки MySQL сервера?

-~{}~ 24.06.08 18:50:

Дополнительный вопрос:
Есть серия запросов, для изменения состояния таблицы:

delete from `list`;
начало цикла
insert into `list` ... ;
конец цикла

Обычно я блокирую и делаю с ней чё хочу. Сначала удаляю оттуда всё, потом записываю по новой.
А если это происходит в транзакции, есть ли смысл блокировки ? Т.е. во время транзакции может какой-нибудь ещё запрос вклиниться между серией запросов? Или при транзаккции происходит автоматическая блокировка?

Спасибо.
 

GrayMaster

Новичок
Начиная от START TRANSACTION, до COMMIT, все выполняемые SQL запросы обрабытываются ведь как одно целое, не так ли?
Если так - то возможность вмешания внешнего запроса внутри транзакции отпадает, или нет?
Вот что собственно интересует... Возможно, мне нужно просто убрать команды LOCK и UNLOCK...
 

Gas

может по одной?
а что понимается под "вмешания внешнего запроса внутри транзакции" ?
Например deadlock'и это пример внешнего вмешания?
 

GrayMaster

Новичок
Имеется ввиду действие над таблицей другим процессом. При использоании блокировки (если таблица заблокированна), добавить или изменить запись не удастся, пока блокировка не снимется. А с транзакцией как ? Аналогично или нет?

Приведу такой пример:

Есть табилца keys (постоянно пополняемая). В ней поле id: VARCHAR (32), PRIMARY KEY.
Ну так вот, пример запроса:

CREATE TEMPORARY TABLE `temp` (`id` VARCHAR (32));
INSERT INTO `temp` SELECT `id` FROM `new_keys` LEFT JOIN `keys` ON `keys`.`id`=`new_keys`.`id` WHERE `new_keys`.`id` IS NULL;
INSERT INTO `keys` SELECT * FROM `temp`;

Тут я создаю временную таблицу, читаю "новые" данные из таблицы new_keys, и пихаю их в keys, где праймари индекс - id.

Если я перед выборкой из new_keys заблокирую таблицу keys, то я буду уверен что при инсерте в keys, не возникнет ошибки повторения ключа. Это в случае блокировки.

А с транзакцией ? Может возникнуть ошибка из за того, что другой процесс в момент от селекта до инсерта, сделает свой инсерт ?
 

gray07

Новичок
Автор оригинала: GrayMaster
А с транзакцией ? Может возникнуть ошибка из за того, что другой процесс в момент от селекта до инсерта, сделает свой инсерт ?
Нажмите для раскрытия...
Зависит от уровня изоляции.
Ссылку вам уже давали.
По умолчанию такое не возникнет
 

Gas

может по одной?
http://www.mysqlperformanceblog.com/2006/07/12/insert-into-select-performance-with-innodb-tables/

до версии 5.1, таблицы из которых идёт выборка лочатся mysql'ем, так что инсерта в этот момент произойти не может.

Но несмотря на это и независимо от уровня изоляции всё равно можно получить duplicate entry error.

В момент пока выполняется первый запрос (вставка в temp), приходит новая транзакция на ставку в keys с ключём, который есть в new_keys и ставится в очередь. После insert temp ... select, когда снимется блокировка, эта новая транзакция будет первее чем "INSERT INTO `keys` SELECT * FROM `temp`;" и этот запрос отвалится.
 

GrayMaster

Новичок
В момент пока выполняется первый запрос (вставка в temp), приходит новая транзакция на ставку в keys с ключём, который есть в new_keys и ставится в очередь. После insert temp ... select, когда снимется блокировка, эта новая транзакция будет первее чем "INSERT INTO `keys` SELECT * FROM `temp`;" и этот запрос отвалится.
Нажмите для раскрытия...
Стоп... Запутался =)

1. START TRANSACTION;
2. CREATE TEMPORARY TABLE `temp` (`id` VARCHAR (32));
3. INSERT INTO `temp` SELECT `id` FROM `new_keys` LEFT JOIN 4. `keys` ON `keys`.`id`=`new_keys`.`id` WHERE `new_keys`.`id` IS NULL;
5. INSERT INTO `keys` SELECT * FROM `temp`;
6. COMMIT;

Допустим 1-6 ээто секунды. В интервале от 4 до 5 параллельный скрипт выполняет: INSERT INTO `temp`;
В итоге, мой INSERT (на 5й секунде), отваливается?
 

Gas

может по одной?
Пример,
Код: 
CREATE TABLE IF NOT EXISTS `keys` (
  `id` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS `keys_new` (
  `id` int(11) NOT NULL
) ENGINE=InnoDB;

TRUNCATE `keys`;
TRUNCATE `keys_new`;
INSERT INTO keys_new(id) VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9);
В одном клиенте запускаешь
Код: 
START TRANSACTION;
INSERT INTO `keys`(id) SELECT IF(sleep(2),NULL,id) FROM keys_new;
INSERT INTO `keys` VALUES(10);
COMMIT;
сразу же в другом (можешь количество секунд в sleep увеличить) запускаешь
Код: 
INSERT INTO `keys` VALUES(10);
И наблюдаем что первая транзакция при попытке добавления 10 отвалилась с ошибкой
"ERROR 1062 (23000): Duplicate entry '10' for key 1"

p.s. CREATE TABLE внутри транзакции вызовет implicit commit, ссылку уже давал. Если autocommit=0 то тут это не важно, но стоит понимать.
 

GrayMaster

Новичок
Спасибо. Проверил - всё так и есть.
Значет перейдем снова к вопросу: как этого избежать? Блокировка - выход, но как я понял, она внутри транзакции работать никак не может?
Или мне лучше блокировать таблицы до транзакции, затем её выполнять??

Кстати транзакцию я делаю только для того, чтоб произошёл откат если что-то пойдет не так. Т.к. много разных изменений делается с табличкой.
 

Popoff

popoff.donetsk.ua
Уважаемые господа!

Идём по моей ссылке, рабираемся с тем, что там написано и используем!

Вот тут ещё есть полезная информация по этому вопросу:
http://en.wikipedia.org/wiki/Isolation_(computer_science)
http://en.wikipedia.org/wiki/Serializable_(databases)
И даже на русском есть:
http://ru.wikipedia.org/wiki/Уровни_изолированности_транзакций

-~{}~ 25.06.08 00:15:

Читаем документацию.

-~{}~ 25.06.08 00:24:

http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-model.html
http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html
 
Статус
В этой теме нельзя размещать новые ответы.
Сверху