VPF::Параллельный выбор данных из таблицы

Гость_Станислав

Дата 26.7.2005, 00:08 (ссылка)

(голосов: 0)

Загрузка ...

Unregistered

Небольшой Callcenter. Агенты при помощи клиентской программы получают контактные телефоны.
Само собой разумеется, различным агентам не должны попасть одинаковые телефоны одновремменно. Хорошо.

Выбираем строку из таблицы, запираем ее(id агента в столбец, скажем "lock_agent_id") и говорим этот агент ее выбрал.

Результат разговора сохраняется и телефон больше не выбирается. Тут все нормально.
Но, сам выбор: допустим

BEGIN
SELECT phone_id,name,phone INTO @phone_id,@name,@phone
FROM phones
WHERE lock_agent_id IS NULL.... LIMIT 1 FOR UPDATE;
UPDATE phones SET lock_agent_id=@agent_id WHERE phone_id = @phone_id;
...
RETURN
END

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

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

Нужеле нет другого выхода? Мне кажется, что это стандартная проблема, но к сожалению, я с ней столкнулся впервые.

Может быть кто-то подскажет. Заранее благодарен.

Akina

Дата 26.7.2005, 08:40 (ссылка)

(нет голосов)

Загрузка ...

Советчик

Профиль
Группа: Модератор
Сообщений: 20581
Регистрация: 8.4.2004
Где: Зеленоград

Репутация: 13
Всего: 454

Сделай наоборот - сперва

Код

update phones set lock_agent_id=@agent_id, lock_time=now() where lock_agent_id is null limit 1

и только потом

Код

select phone_id, name, phone from phones where lock_agent_id=@agent_id order by lock_time desc limit 1

PS. С синтаксисом разбирайся сам - это только идея.

--------------------

О(б)суждение моих действий - в соответствующей теме, пожалуйста. Или в РМ. И высшая инстанция - Администрация форума.

LSD

Дата 26.7.2005, 10:08 (ссылка)

(нет голосов)

Загрузка ...

Leprechaun Software Developer

Профиль
Группа: Модератор
Сообщений: 15718
Регистрация: 24.3.2004
Где: Dublin

Репутация: 24
Всего: 538

Akina не сработает.

В Oracle первый запрос

Код

update phones set lock_agent_id = 1 where ID = (select min(id) from phones where lock_agent_id is null)

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

А в Firebird, поведние не лучше. Второй запрос тоже встает, но после commita вываливается с deadlock -update conflicts with concurrent update.

Здесь можно попробовать использовать select for update, если база поддерживает.

Это сообщение отредактировал(а) LSD - 26.7.2005, 10:09

--------------------

Disclaimer: this post contains explicit depictions of personal opinion. So, if it sounds sarcastic, don't take it seriously. If it sounds dangerous, do not try this at home or at all. And if it offends you, just don't read it.

Гость_Станислав

Дата 26.7.2005, 21:32 (ссылка)

(голосов: 0)

Загрузка ...

Unregistered

"SELECT FOR UPDATE". Пробовал самом начале в Postgres. Результат, как я и написал, - у второго выходит пустая строка, на "READ COMMITTED". На "SERIALIZABLE", - то, что описал LSD.

На MSSQL пробовал "REPEATABLE READ", - тот же результат.

Akina

Дата 26.7.2005, 23:21 (ссылка)

(нет голосов)

Загрузка ...

Советчик

Профиль
Группа: Модератор
Сообщений: 20581
Регистрация: 8.4.2004
Где: Зеленоград

Репутация: 13
Всего: 454

LSD
??? я тебя просто совсем не понимаю... так и должно быть!

Что нужно автору? не налететь... поэтому первый запрос резервирует первую свободную строку БД за текущим челом, а второй просто ищет что именно зарезервировалось - мало занять, надо же сказать что именно было занято...

Знать бы, где это происходит... на MS SQL (с соотв. правкой синтаксиса) это должно работать без проблем на одновременно поступающих запросах, лочить надо (и то - надо ли?) только изменяемую в первом запросе запись, причем что в хранимке, что в триггере, что отдельными запросами...

--------------------

LSD

Дата 27.7.2005, 09:28 (ссылка)

(нет голосов)

Загрузка ...

Leprechaun Software Developer

Профиль
Группа: Модератор
Сообщений: 15718
Регистрация: 24.3.2004
Где: Dublin

Репутация: 24
Всего: 538

Цитата(Akina @ 27.7.2005, 00:21)

Что нужно автору? не налететь... поэтому первый запрос резервирует первую свободную строку БД за текущим челом, а второй просто ищет что именно зарезервировалось - мало занять, надо же сказать что именно было занято...

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

--------------------

Akina

Дата 27.7.2005, 10:05 (ссылка)

(нет голосов)

Загрузка ...

Советчик

Профиль
Группа: Модератор
Сообщений: 20581
Регистрация: 8.4.2004
Где: Зеленоград

Репутация: 13
Всего: 454

а-а-а...

--------------------

LSD

Дата 27.7.2005, 17:22 (ссылка)

(нет голосов)

Загрузка ...

Leprechaun Software Developer

Профиль
Группа: Модератор
Сообщений: 15718
Регистрация: 24.3.2004
Где: Dublin

Репутация: 24
Всего: 538

Кстати Станислав а какая СУБД? В Oracle например такой код работает:

Код

declare
 i raw(16);
begin
 select ID into i from HUMAN where SECOND_NAME is null and rownum = 1 for update;
 update HUMAN set SECOND_NAME = '1' where ID = i;
 commit;
end;
/

--------------------

Guest

Дата 27.7.2005, 22:57 (ссылка)

(голосов: 0)

Загрузка ...

Unregistered

База Postgres

В этом случае у первого проходит все нормально, второй, ждет конца трансакции первого, и в конце концов из функции получает строку, но она пустая.

Код


declare
...
begin
...
select * from PHONES into v_phone where LOCK_AGENT_ID is null limit 1 for update;
update PHONES set LOCK_AGENT_ID = p_agent_id, LOCK_TIME = now() where PHONE_ID = v_phone.PHONE_ID;
...
END

В этом случае, второй, не обращая внимания на уже выполненный UPDATE первого, переписывает LOCK_AGENT_ID и выбирает эту же строку.

Код


declare
...
begin
...
update PHONES set LOCK_AGENT_ID = p_agent_id, LOCK_TIME=now()  
  where PHONE_ID = (select PHONE_ID  from PHONES where LOCK_AGENT_ID is null)

select * from PHONES into v_phone where LOCK_AGENT_ID=p_agent_id;

...
END

Это было на "READ COMMITTED". На "SERIALIZABLE" у второго ошибка "ERROR: could not serialize access due to concurrent update" в первом случае. Второй случай на "SERIALIZABLE" не пробовал. Сейчас попробую

Гость_Станислав

Дата 27.7.2005, 22:59 (ссылка)

(голосов: 0)

Загрузка ...

Unregistered

Забыл ввести свое имя в предыдущем сообщении...

Гость_Станислав

Дата 27.7.2005, 23:18 (ссылка)

(голосов: 0)

Загрузка ...

Unregistered

Во втором случае на serializable тот же результат: ERROR: could not serialize access due to concurrent update

Код


declare
...
begin
...
update PHONES set LOCK_AGENT_ID = p_agent_id, LOCK_TIME=now()  
  where PHONE_ID = (select PHONE_ID  from PHONES where LOCK_AGENT_ID is null limit 1)

select * from PHONES into v_phone where LOCK_AGENT_ID=p_agent_id;

...
END

Stampede

Дата 28.7.2005, 00:24 (ссылка)

(нет голосов)

Загрузка ...

Гносеолог

Профиль
Группа: Участник Клуба
Сообщений: 963
Регистрация: 25.4.2005
Где: Calgary, Alberta, Canada

Репутация: нет
Всего: 144

Цитата

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

Да ну и запирай на здоровье. Там вся транзакция - на микросекунду. Помни о заветах дядьки Оккама smile

--------------------

"If you want something done right, do it yourself"
По секрету: выучить английский - реально!

LSD

Дата 28.7.2005, 09:48 (ссылка)

(нет голосов)

Загрузка ...

Leprechaun Software Developer

Профиль
Группа: Модератор
Сообщений: 15718
Регистрация: 24.3.2004
Где: Dublin

Репутация: 24
Всего: 538

Цитата(Guest @ 27.7.2005, 23:57)

Код


declare
...
begin
...
select * from PHONES into v_phone where LOCK_AGENT_ID is null limit 1 for update;
update PHONES set LOCK_AGENT_ID = p_agent_id, LOCK_TIME = now() where PHONE_ID = v_phone.PHONE_ID;
...
END

А если попробовать:

Код

declare
...
begin
...
select * from PHONES into v_phone where LOCK_AGENT_ID is null for update;
update PHONES set LOCK_AGENT_ID = p_agent_id, LOCK_TIME = now() where PHONE_ID = (select min(PHONE_ID) from v_phone);
...
END

Полностью блокировок избежать не удастся. У нас есть некий общий ресурс (номера телефонов), на который претендуеют несколько пользователей.
Добавлено @ 09:50

Цитата

Забыл ввести свое имя в предыдущем сообщении...

Зарегистрируйся и заходи почаще smile

Это сообщение отредактировал(а) LSD - 28.7.2005, 09:48

--------------------

Гость_Станислав

Дата 28.7.2005, 21:28 (ссылка)

(голосов: 0)

Загрузка ...

Unregistered

LSD

declare
...

Код


begin
...
select * from PHONES into v_phone where LOCK_AGENT_ID is null for update;
update PHONES set LOCK_AGENT_ID = p_agent_id, LOCK_TIME = now() where PHONE_ID = (select min(PHONE_ID) from v_phone);
...
END

Запирается вся таблица, вернее те строки, где пустой "LOCK_AGENT_ID". Т.е. те строки которые имеет уже номер агента остаются открытым. Следовательно агенты, которые в данный момент разговаривают по телефону смогут без проблем сохранить результат, и сказать номер отработат, - эту уже лучше, чем запирать всю таблицу. Но нужно еще проверить, пока что сомневаюсь, что сработает.

LSD

Дата 28.7.2005, 22:01 (ссылка)

(нет голосов)

Загрузка ...

Leprechaun Software Developer

Профиль
Группа: Модератор
Сообщений: 15718
Регистрация: 24.3.2004
Где: Dublin

Репутация: 24
Всего: 538

Я не написал, но после update PHONES..., должен идити commit. Т.е. Все свободняе номера телефонов, запираются только на момент "захвата" очередного номера, как только номер "захвачен" блокировка снимается.

--------------------

0 Пользователей читают эту тему (0 Гостей и 0 Скрытых Пользователей)
0 Пользователей:
« Предыдущая тема \| СУБД, общие вопросы \| Следующая тема »