Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Подскажите по read committed  [new]
Б.Г.Мотик
Member

Откуда:
Сообщений: 11
Привет сообществу, помогите советом (или, точнее ответом :)).

Вставка в таблицу нескольких записей - операция атомарная?

Т.е. если вставляешь в таблицу [table] 10000 записей запросом вида
insert into [table] select from [view] where ...

(например), может ли select, запущенный в другой сессии, увидеть часть записей, или он обязательно увидит/не увидит их все?

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

Однако.

Если записи приняты, гарантируется ли что все 10 тыс записей будут доступны для запросов в других сессиях в точности одновременно?

Уровень изоляции транзакций read committed, "неповторяемое чтение" возможно. Вопрос, насколько широко можно данный термин. Возможно ли "неповторяемое чтение" результатов одного и того же инсерта?

Сервер 2014, мать его за ногу. Microsoft SQL Server 2014 - 12.0.4213.0 (X64)
17 фев 17, 16:43    [20222093]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите по read committed  [new]
лолл
Member

Откуда:
Сообщений: 450
Б.Г.Мотик,

никто не увидит часть транзакции, пока она успешно не завершится, т.е. пока все записи не будут добавлены в таблицу, при любом уровне изоляции, он тут не при чем. таковы ACID-требования к транзакциям
17 фев 17, 17:05    [20222171]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите по read committed  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37069
лолл
никто не увидит часть транзакции, пока она успешно не завершится, т.е. пока все записи не будут добавлены в таблицу, при любом уровне изоляции
Т.е. read uncommitted уже за уровень изоляции не считается?

Сообщение было отредактировано: 17 фев 17, 17:11
17 фев 17, 17:11    [20222201]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите по read committed  [new]
лолл
Member

Откуда:
Сообщений: 450
Гавриленко Сергей Алексеевич,

насколько я понял, речь идет о том, возможно ли увидеть часть вставляемых данных оператором INSERT, который начал выполнять вставку но еще не закончил...
17 фев 17, 17:26    [20222240]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите по read committed  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37069
лолл
Гавриленко Сергей Алексеевич,

насколько я понял, речь идет о том, возможно ли увидеть часть вставляемых данных оператором INSERT, который начал выполнять вставку но еще не закончил...
И? Что помешает прочитать эти записи в read uncommitted?
17 фев 17, 17:27    [20222244]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите по read committed  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
лолл,

конечно можно, это так называемое "грязное чтение", уровень изоляции Read Uncommitted.
17 фев 17, 17:28    [20222246]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите по read committed  [new]
лолл
Member

Откуда:
Сообщений: 450
Ах, да, понял вас :)
17 фев 17, 17:30    [20222261]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите по read committed  [new]
Б.Г.Мотик
Member

Откуда:
Сообщений: 11
Спасибо за ответы.

1. Уровень изоляции - по умолчанию, т.е. READ COMMITTED

2. Не спорю: "никто не увидит часть транзакции, пока она успешно не завершится".

Вопрос, что будет, когда она "успешно завершится". Т.е. после того, как "все записи добавлены в таблицу" - мгновенно ли они становятся доступны для других запросов, в том числе начавшихся до завершения транзакции?

Вопрос совершенно практический, есть два десятка параллельно выполняющихся селектов из таблицы, которая все время пополняется. Нужно гарантировать, что две определенных строки доступны для селектов только вместе. Я могу гарантировать, что обе строки вставляются одним инсертом - но время от времени наблюдаю картину, как если бы одна строка присутствовала в таблице в момент выполнения одного из запросов, а вторая еще не присутствовала. Данных в таблице не много, до 50 тысяч строк в сутки, но запросы тяжелые (среднее время выполнения 30 секунд, отмечу, за счет конкуренции между запросами). В чем может быть причина сбоев?
17 фев 17, 18:17    [20222496]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите по read committed  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37069
Б.Г.Мотик
но время от времени наблюдаю картину
Наблюдаете где? Какими командами?
Б.Г.Мотик
В чем может быть причина сбоев?
В вашем коде.
17 фев 17, 18:30    [20222528]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите по read committed  [new]
Б.Г.Мотик
Member

Откуда:
Сообщений: 11
Гавриленко Сергей Алексеевич,

"В вашем коде" - да, разумеется. Интересуют альтернативные варианты.
17 фев 17, 18:34    [20222544]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите по read committed  [new]
msLex
Member

Откуда:
Сообщений: 8191
Б.Г.Мотик
Спасибо за ответы.

1. Уровень изоляции - по умолчанию, т.е. READ COMMITTED

2. Не спорю: "никто не увидит часть транзакции, пока она успешно не завершится".

Вопрос, что будет, когда она "успешно завершится". Т.е. после того, как "все записи добавлены в таблицу" - мгновенно ли они становятся доступны для других запросов, в том числе начавшихся до завершения транзакции?

Вопрос совершенно практический, есть два десятка параллельно выполняющихся селектов из таблицы, которая все время пополняется. Нужно гарантировать, что две определенных строки доступны для селектов только вместе. Я могу гарантировать, что обе строки вставляются одним инсертом - но время от времени наблюдаю картину, как если бы одна строка присутствовала в таблице в момент выполнения одного из запросов, а вторая еще не присутствовала. Данных в таблице не много, до 50 тысяч строк в сутки, но запросы тяжелые (среднее время выполнения 30 секунд, отмечу, за счет конкуренции между запросами). В чем может быть причина сбоев?



В read committed возможны варианты, когда "селект" вычитает только часть вставленных записей.
17 фев 17, 18:36    [20222550]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите по read committed  [new]
Б.Г.Мотик
Member

Откуда:
Сообщений: 11
Для большинства людей конец транзакции, понимаемый, как невозможность отката, в точности совпадает с моментом, начиная с которого результаты транзакции становятся доступны для других транзакций.

Интересует, правильно ли это понимание.
17 фев 17, 18:40    [20222570]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите по read committed  [new]
Б.Г.Мотик
Member

Откуда:
Сообщений: 11
msLex
Б.Г.Мотик
Вопрос совершенно практический, есть два десятка параллельно выполняющихся селектов из таблицы, которая все время пополняется. Нужно гарантировать, что две определенных строки доступны для селектов только вместе. Я могу гарантировать, что обе строки вставляются одним инсертом - но время от времени наблюдаю картину, как если бы одна строка присутствовала в таблице в момент выполнения одного из запросов, а вторая еще не присутствовала. Данных в таблице не много, до 50 тысяч строк в сутки, но запросы тяжелые (среднее время выполнения 30 секунд, отмечу, за счет конкуренции между запросами). В чем может быть причина сбоев?



В read committed возможны варианты, когда "селект" вычитает только часть вставленных записей.


О! Оно :). Есть ли какие-нибудь подробности?
17 фев 17, 18:43    [20222576]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите по read committed  [new]
msLex
Member

Откуда:
Сообщений: 8191
Ну, чтоб не быть голословным


-- в первом коннекте (окне ssms) создаем таблицу и запускаем добавление по 5 записей в бесконечном цикле случайными значениями

if OBJECT_ID('dbo.TestTable') is not null
	drop table dbo.TestTable

create table dbo.TestTable (
	id int not null
)
create clustered index ix on dbo.TestTable(id)

 

while 1=1
	insert dbo.TestTable (
		id
	)
	select top 5 
		CHECKSUM(NEWID())
	from sys.objects


-- во втором окне считаем количество строк в таблице в бесконечном цикле 

while 1=1 
	select COUNT(*)	
	from dbo.TestTable



-- радуемся количествам не кратным 5 
17 фев 17, 18:47    [20222587]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите по read committed  [new]
iljy
Member

Откуда:
Сообщений: 8711
Б.Г.Мотик
msLex
пропущено...



В read committed возможны варианты, когда "селект" вычитает только часть вставленных записей.


О! Оно :). Есть ли какие-нибудь подробности?



Подробности более чем банальны: select читающий данные в последовательности индекса, в другой транзакции insert, вставляющий данные в разные места этого самого индекса. select читает данные 1,3,4,5,7...., insert вставляет 2,8,12. select прочитает 8 и 12, но не прочитает 2 (потому как с той страницы он уже ушел), но 8 и 12 он прочитает ПОСЛЕ того, как insert полностью завершится.
17 фев 17, 19:06    [20222635]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите по read committed  [new]
invm
Member

Откуда: Москва
Сообщений: 9405
Б.Г.Мотик
Нужно гарантировать, что две определенных строки доступны для селектов только вместе.
RCSI вам поможет.
Либо serializable у читателей, но тогда будут конфликты с писателями.
17 фев 17, 19:11    [20222639]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите по read committed  [new]
Б.Г.Мотик
Member

Откуда:
Сообщений: 11
msLex
Ну, чтоб не быть голословным
...


Спасибо, прекрасный пример! Что я отметил, на 2005-том и 2008-мом серверах второй из "бесконечных" циклов в какой-то момент завершается с ошибкой

Msg 1205, Level 13, State 52, Line 3
Transaction (Process ID XXX) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

На 2014-том работает, хотя это может быть вопрос не версии, а производительности системы (количество памяти, проч.)




iljy
Подробности более чем банальны: select читающий данные в последовательности индекса, в другой транзакции insert, вставляющий данные в разные места этого самого индекса. select читает данные 1,3,4,5,7...., insert вставляет 2,8,12. select прочитает 8 и 12, но не прочитает 2 (потому как с той страницы он уже ушел), но 8 и 12 он прочитает ПОСЛЕ того, как insert полностью завершится.


По логике, это касается не только поиска в индексе; выходит, что с READ COMMITTED запрос может не прочитать часть строк, вставленных недавно зафиксированными транзакциями, и если я хочу, чтобы строка B была прочитана только вместе со строкой A, то даже разнести вставку B и вставку A по разным транзакциям, и выполнять вторую всегда перед первой не будет полезно.



invm
RCSI вам поможет.
Либо serializable у читателей, но тогда будут конфликты с писателями.


serializable не подходит, пробовал в схожей задаче, действительно, были дидлоки. А снапшот не помог :(. По идее, должно было выйти - при read committed snapshot запрос как бы обязан игнорировать данные транзакций, зафиксированных после того, как он начался и, по идее, обязан использовать данные, зафиксированные до. Но на практике проблема не решилась.

Что я именно делал:
1. Включил снапшот на уровне базы (поставил в свойствах Allow snapshot isolation = True)
2. Поставил подсказку with (readcommitted) возле каждого вхождения таблицы [table] в запросе-читателе.

В документации по with (readcommitted) читаем: "Если значение параметра базы данных READ_COMMITTED_SNAPSHOT равно ON, компонент Компонент Database Engine не накладывает блокировок и использует управление версиями строк" ф(https://msdn.microsoft.com/ru-ru/library/ms187373.aspx). Но в реальности, профита нет.

Я что-то сделал не так?
20 фев 17, 10:55    [20227678]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите по read committed  [new]
invm
Member

Откуда: Москва
Сообщений: 9405
Б.Г.Мотик
Что я именно делал:
1. Включил снапшот на уровне базы (поставил в свойствах Allow snapshot isolation = True)
2. Поставил подсказку with (readcommitted) возле каждого вхождения таблицы [table] в запросе-читателе.
Б.Г.Мотик
Я что-то сделал не так?
Ага, не так.
Включить нужно было "Is Read Committed Snapshot On"
Никаких подсказок не требуется, если и так работаете на RC.
20 фев 17, 11:08    [20227714]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите по read committed  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
Б.Г.Мотик,

какая разница, какие строки будут от какой транзакции прочитаны? Каждая строка в одной таблице независима и не оказывает влияния на другую. Важно обеспечить целостность данных при вставке в разные таблицы, что гарантирует вставка в общей транзакции.
20 фев 17, 11:17    [20227751]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите по read committed  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
Речь не идет о внешнем ключе на эту же таблицу, разумеется.
20 фев 17, 11:19    [20227758]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите по read committed  [new]
Б.Г.Мотик
Member

Откуда:
Сообщений: 11
invm,

Дело в том, что мне нужно, чтобы все таблицы, коме одной обрабатывались с наложением блокировок, и только одна - на основании версий строк.

Для понимания, я запускаю процессы с учетом результатов предыдущих запусков. Отбор процесса (несколько разных таблиц) лучше производить с наложением блокировок - ну, по крайней мере, так кажется :), - однако отбор результатов (таблица [table]) - с использованием версий строк. Поскольку результат обработки процесса представлен несколькими записями в [table], но должен обрабатываться атомарно.

Установить read commiited snaphot isolation для всей базы в целом я не могу.
20 фев 17, 11:26    [20227792]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите по read committed  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2421
Б.Г.Мотик,

автор
serializable не подходит, пробовал в схожей задаче, действительно, были дидлоки.


Это ваш единственный вариант, а то что он вам не помог, вы просто его не правильно приготовили, попробуйте еще раз но с tablockx, а чтобы небыло дедлоков, очередность доступа к таблицам(очередность наложения блокировок) во всех процедурах(транзакциях) должна быть одинаковой, тогда не будет перехлеста и как следствие блокировки не превратятся в дедлоки.
20 фев 17, 11:31    [20227824]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите по read committed  [new]
Б.Г.Мотик
Member

Откуда:
Сообщений: 11
Владислав Колосов
Б.Г.Мотик,

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


Необходимо, чтобы группа записей была прочитана либо не прочитана только вместе или, менее строгое требование, в определенном порядке. Как я выше писал, нужно чтобы строка B была прочитана запросом только вместе со строкой A, или не прочитана вообще (т.е. прочитана в будущем).
20 фев 17, 11:32    [20227831]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите по read committed  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
Б.Г.Мотик,

нумеруйте вставки, в таком случае, и читайте по номерам.
20 фев 17, 11:33    [20227834]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите по read committed  [new]
Б.Г.Мотик
Member

Откуда:
Сообщений: 11
WarAnt,

tablock и пробовал :), на счет очередности - да, не экспериментировал
20 фев 17, 11:35    [20227846]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить