MSSQL 2000
Имеется большая транзакция, в которой вызываются различные хранимые процедуры, которые выполняют UPDATE, INSERT и DELETE в различных таблицах. В результате все таблицы блокируются до конца транзакции. Но другим программам нужно чиать данные из этих таблиц.

Как сделать так, чтобы таблицы блокировались не на всю транзакцию? либо задать другой режим блокирования? Какие параметры блокирования нужно указать
в INSERT ... WITH(...).
Какой Isolation level нужно задать транзакции?

Здравствуйте Blazkowicz, Вы писали:

B>MSSQL 2000
B>Имеется большая транзакция, в которой вызываются различные хранимые процедуры, которые выполняют UPDATE, INSERT и DELETE в различных таблицах. В результате все таблицы блокируются до конца транзакции. Но другим программам нужно чиать данные из этих таблиц.

Данные, для которых сделали UPDATE, INSERT или DELETE в транзакции ты ничем не разблокируешь. Иначе потом rollback был бы невозможен. Хинты и уровень изоляции работают только на чтение.

Можешь читающим транзакциям поставить уровень изоляции READUNCOMMITED, если тебя устроит возможное чтение незакомиченных данных.

Здравствуйте Lexey, Вы писали:

L>Можешь читающим транзакциям поставить уровень изоляции READUNCOMMITED, если тебя устроит возможное чтение незакомиченных данных.

А нельзя сделать так, чтобы читались данные, которые были в таблицах до начала транзакции?

Здравствуйте Blazkowicz, Вы писали:

L>>Можешь читающим транзакциям поставить уровень изоляции READUNCOMMITED, если тебя устроит возможное чтение незакомиченных данных.

B>А нельзя сделать так, чтобы читались данные, которые были в таблицах до начала транзакции?

Нет.

Здравствуйте Blazkowicz, Вы писали:

B>А нельзя сделать так, чтобы читались данные, которые были в таблицах до начала транзакции?
Это в версионниках, а MSSQL — блокировочник...
Можешь попробовать извратиться с хинтами на уровень блокировки, чтобы у тебя не вся таблица блокировалась, а только записи которые изменяются. WITH(ROWLOCK) например.
Но там есть засада, сейчас уже не помню, но кажется ROWLOCK действует только если есть индекс по вусловию WHERE.
Вообще странно, что блокируется вся таблица, при наличии индексов должен идти key-range lock, хотя если запросы очень большие и сложные, то...

Здравствуйте Merle, Вы писали:

M>Здравствуйте Blazkowicz, Вы писали:

B>>А нельзя сделать так, чтобы читались данные, которые были в таблицах до начала транзакции?
M>Это в версионниках, а MSSQL — блокировочник...
M>Можешь попробовать извратиться с хинтами на уровень блокировки, чтобы у тебя не вся таблица блокировалась, а только записи которые изменяются. WITH(ROWLOCK) например.

MSSQL и так по дефолту пытается отдельные записи или регионы блокировать.

M>Но там есть засада, сейчас уже не помню, но кажется ROWLOCK действует только если есть индекс по вусловию WHERE.

Вряд ли. Скорее всего он и по PK умеет блокировать. Вот для range-лока действительно нужен индекс по условию.

M>Вообще странно, что блокируется вся таблица, при наличии индексов должен идти key-range lock, хотя если запросы очень большие и сложные, то...

Там есть другая засада. Если row-локов или range-локов становится много, то локи поднимаются до более объемлющих.

Здравствуйте Lexey, Вы писали:

L>Здравствуйте Blazkowicz, Вы писали:

B>>MSSQL 2000
B>>Имеется большая транзакция, в которой вызываются различные хранимые процедуры, которые выполняют UPDATE, INSERT и DELETE в различных таблицах. В результате все таблицы блокируются до конца транзакции. Но другим программам нужно чиать данные из этих таблиц.

L>Данные, для которых сделали UPDATE, INSERT или DELETE в транзакции ты ничем не разблокируешь. Иначе потом rollback был бы невозможен. Хинты и уровень изоляции работают только на чтение.

L>Можешь читающим транзакциям поставить уровень изоляции READUNCOMMITED, если тебя устроит возможное чтение незакомиченных данных.

На самом деле транзакция так блокирует таблицы что из них даже ничего нельзя прочитать.
Т.е. хотелось бы на время выполнения транзакции считывать "старые данные"

Здравствуйте gleb_rsdn, Вы писали:

GR>Здравствуйте Lexey, Вы писали:

L>>Здравствуйте Blazkowicz, Вы писали:

B>>>MSSQL 2000
B>>>Имеется большая транзакция, в которой вызываются различные хранимые процедуры, которые выполняют UPDATE, INSERT и DELETE в различных таблицах. В результате все таблицы блокируются до конца транзакции. Но другим программам нужно чиать данные из этих таблиц.

L>>Данные, для которых сделали UPDATE, INSERT или DELETE в транзакции ты ничем не разблокируешь. Иначе потом rollback был бы невозможен. Хинты и уровень изоляции работают только на чтение.

L>>Можешь читающим транзакциям поставить уровень изоляции READUNCOMMITED, если тебя устроит возможное чтение незакомиченных данных.

GR>На самом деле транзакция так блокирует таблицы что из них даже ничего нельзя прочитать.
GR>Т.е. хотелось бы на время выполнения транзакции считывать "старые данные"

Сорри, не видел последние ответы, поэтому задал устаревший вопрос.
Т.е. я так понимаю что MS SQL пытается установить оптимальную блокировку, в зависимости от того что делает транзакция?
И типа лучще оставить все как есть?

Здравствуйте gleb_rsdn, Вы писали:

L>>Данные, для которых сделали UPDATE, INSERT или DELETE в транзакции ты ничем не разблокируешь. Иначе потом rollback был бы невозможен. Хинты и уровень изоляции работают только на чтение.

L>>Можешь читающим транзакциям поставить уровень изоляции READUNCOMMITED, если тебя устроит возможное чтение незакомиченных данных.

GR>На самом деле транзакция так блокирует таблицы что из них даже ничего нельзя прочитать.

Такое возможно только при эксклюзивном локе на таблицу. Такие локи, по идее, ставят только стейтменты, модифицирующие схему таблицы. К UPDATE/INSERT/DELETE это не относится.

GR>Т.е. хотелось бы на время выполнения транзакции считывать "старые данные"

Не получится.

Здравствуйте gleb_rsdn, Вы писали:

GR>Сорри, не видел последние ответы, поэтому задал устаревший вопрос.
GR>Т.е. я так понимаю что MS SQL пытается установить оптимальную блокировку, в зависимости от того что делает транзакция?

Да.

GR>И типа лучще оставить все как есть?

Не всегда. Иногда хинтами можно улучшить ситуацию (а можно и ухудшить).
Честно говоря, с хинтами, определяющими scope локов, я пока ни разу не рисковал играться. И вероятно и не стану, а вот хинты типа REPEATABLEREAD, UPDATELOCK и т.п., определяющие время жизни и тип лока иногда помогают.

Вообще для этого случая в MS SQL есть только одна дейтвительно работающая тактика:

Большинство запросов на выборку на самом деле не нуждаются в уровне изоляции READ COMMITTED, некоторая несинхронизированность также добпустима, и это можно проиллюстрировать. Поэтому на такие объёмлющие выборки всегда ходим с with(nolock) option.

Пратически все запросы за обновление и вставку, если они не массовые, желательно объявлять with(rowlock) option. Т.к., к сожалению, эскалация блокировок сервера работает крайне неудачно, вызывая deadlocks гораздо раньше, чем следует. С этой опцией серверу приходиться тяжелее, но хотя бы можно работать.

При этом общий уровень изоляции лучше оставить READ COMMITTED.

Это моё личное мнение.

Здравствуйте Akzhan, Вы писали:


A>Большинство запросов на выборку на самом деле не нуждаются в уровне изоляции READ COMMITTED, некоторая несинхронизированность также добпустима, и это можно проиллюстрировать. Поэтому на такие объёмлющие выборки всегда ходим с with(nolock) option.

With(nolock) нужно ставить в SELECT — запросах внутри транзакции? Они что, по умолчанию тоже что-то блокируют? Или эту опцию нужно выставлять в запросах которые читают данные, блокируемые транзакцией?

Здравствуйте Blazkowicz, Вы писали:

A>>Большинство запросов на выборку на самом деле не нуждаются в уровне изоляции READ COMMITTED, некоторая несинхронизированность также добпустима, и это можно проиллюстрировать. Поэтому на такие объёмлющие выборки всегда ходим с with(nolock) option.

B>With(nolock) нужно ставить в SELECT — запросах внутри транзакции? Они что, по умолчанию тоже что-
то блокируют? Или эту опцию нужно выставлять в запросах которые читают данные, блокируемые транзакцией?

Естественно селекты блокируют, а ты как думал? Причем тип и длительность блокировки зависит от уровня изоляции транзакции.
READ UNCOMITTED (тоже самое, что хинт NOLOCK) — блокировка только схемы таблицы до конца чтения
READ COMITTED (дефолт) — shared блокировка до конца чтения
REPEATABLE READ — shared блокировка до конца транзакции
SERIALIZABLE — shared key-range блокировка до конца транзакции (она не позволяет вставлять новые записи в range).

Соответсвенно, with(nolock) нужно ставить селектам в читающих транзакциях. Внутри пишуших транзакций такого точно лучше не делать.

Здравствуйте Lexey, Вы писали:

L>Соответсвенно, with(nolock) нужно ставить селектам в читающих транзакциях. Внутри пишуших транзакций такого точно лучше не делать.

Угу, пасибочки, вот только тут проблема в том, что и эта пишущая транзакция и читающие используют одни и те же функции, выполняющие SELECT.

мы тут ещё поковырялись получилось вот что:
Блокируется какая-то запись в таблице. В результате я могу читать все строки кроме той, которая заблокирована.
Поэтому

SELECT * 
FROM table
WHERE ID = чему-то

работает. А вот

SELECT * 
FROM table

Не работает до конца транзакиции, заблокировавшей 1 запись в таблице.
Можно ли как-то организовать Выборку всего кроме того что заблокировано?

Здравствуйте Blazkowicz, Вы писали:

B>Не работает до конца транзакиции, заблокировавшей 1 запись в таблице.
B>Можно ли как-то организовать Выборку всего кроме того что заблокировано?

READPAST Skip locked rows. This option causes a transaction to skip rows locked by other transactions that would ordinarily appear in the result set, rather than block the transaction waiting for the other transactions to release their locks on these rows. The READPAST lock hint applies only to transactions operating at READ COMMITTED isolation and will read only past row-level locks. Applies only to the SELECT statement.

Здравствуйте Lexey, Вы писали:


L>READPAST Skip locked rows. This option causes a transaction to skip rows
L>...
L>Applies only to the SELECT statement.

О!!! Пасиба!