Блокировка таблицы разными хранимыми процедурами / Sybase ASA, ASE, IQ

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / Sybase ASA, ASE, IQ [игнор отключен] [закрыт для гостей] / Блокировка таблицы разными хранимыми процедурами

6 сообщений из 6, страница 1 из 1

Блокировка таблицы разными хранимыми процедурами

#38303828

lioner

Гость

Здравствуйте, уважаемые.
ASA 9.0.2.3486.
Есть таблица:

Код: sql

1.
2.
3.
4.
5.
6.

create table _test_table
(
 proc1 integer default 0,
 proc2 integer default 0
);
insert into _test_table(proc1,proc2) values(0,0);

В ней 2 поля-признака для 2-х процедур: proc1 = 1 когда процедура _proc1 выполняется, proc2 = 1 когда процедура _proc2 выполняется.
Вот сами процедуры:

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.

create procedure _proc1()
begin
 declare isp integer;
 declare i integer;
message 'start _proc1, now=',now(*);
 select proc1 into isp from _test_table;
 if isp<>1 then
message '_proc1 before update proc1=1, now=',now(*);
  update _test_table set proc1=1;
message '_proc1 after update proc1=1, now=',now(*);
  set i=0;
  while i<=1000000 loop
   set i=i+1;
  end loop;
message '_proc1 before update proc1=0, now=',now(*);
  update _test_table set proc1=0;
message '_proc1 after update proc1=0, now=',now(*);
  commit work
 else
  raiserror 99999 'Процедура _proc1 в работе. Ждите ее завершения.';
 end if;
message 'end _proc1, now=',now(*);
exception
 when others then
  message 'ERROR!!!! Information: sqlstate=',sqlstate,', sqlcode=',sqlcode,', message=',errormsg(*);
  rollback work;
end;
create procedure _proc2()
begin
 declare isp integer;
 declare i integer;
message 'start _proc2, now=',now(*);
 select proc2 into isp from _test_table;
 if isp<>1 then
message '_proc2 before update proc2=1, now=',now(*);
  update _test_table set proc2=1;
message '_proc2 after update proc2=1, now=',now(*);
  set i=0;
  while i<=1000000 loop
   set i=i+1;
  end loop;
message '_proc2 before update proc2=0, now=',now(*);
  update _test_table set proc2=0;
message '_proc2 after update proc2=0, now=',now(*);
  commit work
 else
  raiserror 99999 'Процедура _proc2 в работе. Ждите ее завершения.';
 end if;
message 'end _proc2, now=',now(*);
exception
 when others then
  message 'ERROR!!!! Information: sqlstate=',sqlstate,', sqlcode=',sqlcode,', message=',errormsg(*);
  rollback work;
end;

Мессаджи специально прописал, чтобы было видно ход выполнения процедур.
В одном Interactive SQL вызываю:

Код: sql

call _proc1();

В другом Interactive SQL вызываю:

Код: sql

call _proc2();

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

...

Рейтинг:

0 / 0

19.06.2013, 18:45

| Ответить | Цитировать | Написать

Блокировка таблицы разными хранимыми процедурами

#38303869

White Owl

Участник

Сообщения: 12 726

Рейтинг: 0 / 0

Сделай не два поля в одной записи, а по одной записи на процедуру.

Код: sql

1.
2.
3.
4.

create table mylocks (
  proc_name varchar(32) primary key,
  locked       bit
);

Читай тут:
http://infocenter.sybase.com/help/topic/com.sybase.help.sqlanywhere.12.0.0/dbusage/update-how-transact.html

...

Рейтинг:

0 / 0

19.06.2013, 19:15

| Ответить | Цитировать | Написать

Блокировка таблицы разными хранимыми процедурами

#38304479

lioner

Гость

White OwlСделай не два поля в одной записи, а по одной записи на процедуру.

Код: sql

1.
2.
3.
4.

create table mylocks (
  proc_name varchar(32) primary key,
  locked       bit
);

Читай тут:
http://infocenter.sybase.com/help/topic/com.sybase.help.sqlanywhere.12.0.0/dbusage/update-how-transact.html

К сожалению структуру таблицы нельзя менять. Там должна быть одна запись с полями для многих процедур. Хотя идея хранить флаг для каждой процедуры в разных записях решило бы проблему.

...

Рейтинг:

0 / 0

20.06.2013, 12:05

| Ответить | Цитировать | Написать

Блокировка таблицы разными хранимыми процедурами

#38304547

lioner

Гость

Пробовал прописывать явно коммиты после апдейта записи в процедурах.

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.

ALTER PROCEDURE "DBA"."_proc1"()
begin
 declare isp integer;
 declare i integer;
message 'start _proc1, now=',now(*);
 select proc1 into isp from _test_table;
 if isp<>1 then
message '_proc1 before update proc1=1, now=',now(*);
  update _test_table set proc1=1;
  commit;
message '_proc1 after update proc1=1, now=',now(*);
  set i=0;
  while i<=100000000 loop
   set i=i+1;
  end loop;
message '_proc1 before update proc1=0, now=',now(*);
  update _test_table set proc1=0;
  commit;
message '_proc1 after update proc1=0, now=',now(*);
  commit work
 else
  raiserror 99999 'Процедура _proc1 в работе. Ждите ее завершения.';
 end if;
message 'end _proc1, now=',now(*);
exception
 when others then
  message 'ERROR!!!! Information: sqlstate=',sqlstate,', sqlcode=',sqlcode,', message=',errormsg(*);
  rollback work;
end

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.

ALTER PROCEDURE "DBA"."_proc2"()
begin
 declare isp integer;
 declare i integer;
message 'start _proc2, now=',now(*);
 select proc2 into isp from _test_table;
 if isp<>1 then
message '_proc2 before update proc2=1, now=',now(*);
  update _test_table set proc2=1;
  commit;
message '_proc2 after update proc2=1, now=',now(*);
  set i=0;
  while i<=100000000 loop
   set i=i+1;
  end loop;
message '_proc2 before update proc2=0, now=',now(*);
  update _test_table set proc2=0;
  commit;
message '_proc2 after update proc2=0, now=',now(*);
  commit work
 else
  raiserror 99999 'Процедура _proc2 в работе. Ждите ее завершения.';
 end if;
message 'end _proc2, now=',now(*);
exception
 when others then
  message 'ERROR!!!! Information: sqlstate=',sqlstate,', sqlcode=',sqlcode,', message=',errormsg(*);
  rollback work;
end

При этом сообщения сервера такие:

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.

start _proc1, now=2013-06-20 11:10:31.593
_proc1 before update proc1=1, now=2013-06-20 11:10:31.593
_proc1 after update proc1=1, now=2013-06-20 11:10:31.609
start _proc2, now=2013-06-20 11:10:32.640
_proc2 before update proc2=1, now=2013-06-20 11:10:32.640
_proc2 after update proc2=1, now=2013-06-20 11:10:32.640
_proc1 before update proc1=0, now=2013-06-20 11:11:02.468
_proc1 after update proc1=0, now=2013-06-20 11:11:02.468
end _proc1, now=2013-06-20 11:11:02.468
_proc2 before update proc2=0, now=2013-06-20 11:11:02.750
_proc2 after update proc2=0, now=2013-06-20 11:11:02.750
end _proc2, now=2013-06-20 11:11:02.750

Из чего видно:
1) _proc1 запустилась
2) _proc1 пометило запись в таблице, что она запущена, и стала выполнять свой цикл
3) _proc2 запустилась
4) _proc2 пометило запись в таблице, что она запущена, и стала выполнять свой цикл
5) _proc1 выполнило свой цикл и пометило запись в таблице, что она закончила работу, и завершилась
6) _proc2 выполнило свой цикл и пометило запись в таблице, что она закончила работу, и завершилась
При этом время выполнения каждой из процедур 30-31 сек, как пишет интерактив в execution time.

Если коммиты в процедурах закомментировать и выполнить еще раз, то сообщения сервера такие:

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.

start _proc1, now=2013-06-20 11:20:40.359
_proc1 before update proc1=1, now=2013-06-20 11:20:40.359
_proc1 after update proc1=1, now=2013-06-20 11:20:40.359
start _proc2, now=2013-06-20 11:20:41.500
_proc2 before update proc2=1, now=2013-06-20 11:20:41.500
_proc1 before update proc1=0, now=2013-06-20 11:21:10.109
_proc1 after update proc1=0, now=2013-06-20 11:21:10.125
_proc2 after update proc2=1, now=2013-06-20 11:21:10.125
end _proc1, now=2013-06-20 11:21:10.125
_proc2 before update proc2=0, now=2013-06-20 11:21:39.453
_proc2 after update proc2=0, now=2013-06-20 11:21:39.468
end _proc2, now=2013-06-20 11:21:39.468

Из чего видно:
1) _proc1 запустилась
2) _proc1 пометило запись в таблице, что она запущена, и стала выполнять свой цикл
3) _proc2 запустилась и готова пометить запись в таблице, что она запущена, но ждет пока таблицу отпустит _proc1
5) _proc1 выполнило свой цикл и пометило запись в таблице, что она закончила работу
6) _proc2 пометило запись в таблице, что она запущена, и стала выполнять свой цикл
7) _proc1 завершилась
8) _proc2 выполнило свой цикл и пометило запись в таблице, что она закончила работу, и завершилась

При этом время выполнения каждой из процедур уже около минуты, как пишет интерактив в execution time.

Можно ли вот так явно прописывать COMMIT в процедурах, чтобы избавиться от блокирования таблицы и не всплывут ли из-за этого подводные камни и потенциальные ошибки ?

...

Рейтинг:

0 / 0

20.06.2013, 12:29

| Ответить | Цитировать | Написать

Блокировка таблицы разными хранимыми процедурами

#38304566

antand

Гость

lioner,

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

...

Рейтинг:

0 / 0

20.06.2013, 12:35

| Ответить | Цитировать | Написать

Блокировка таблицы разными хранимыми процедурами

#38304765

lioner

Гость

Спасибо всем за помощь.
Создание новой таблицы и процедуры установки/снятия флага решило проблему.

...

Рейтинг:

0 / 0

20.06.2013, 13:48

| Ответить | Цитировать | Написать

6 сообщений из 6, страница 1 из 1

Форумы / Sybase ASA, ASE, IQ [игнор отключен] [закрыт для гостей] / Блокировка таблицы разными хранимыми процедурами

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=55&msg=38303869&tid=2009958]:	0ms
get settings:	10ms
get forum list:	14ms
check forum access:	4ms
check topic access:	4ms
track hit:	154ms
get topic data:	12ms
get forum data:	3ms
get page messages:	48ms
get tp. blocked users:	1ms
others:	17ms

total:	267ms