SQL Server ВСТАВЛЯЕТ, Scope_Identity () и физическая запись в диск
У меня есть хранимая процедура, которая делает, среди другого материала, некоторых вставок в другой таблице в цикле. Посмотрите пример ниже для более ясного понимания:
INSERT INTO T1 VALUES ('something')
SET @MyID = Scope_Identity()
... some stuff go here
INSERT INTO T2 VALUES (@MyID, 'something else')
... The rest of the procedure
Эти две таблицы (T1 и T2) имеют ИДЕНТИФИКАЦИОННЫЕ ДАННЫЕ (1, 1) столбец в каждом из них, давайте назовем их ID1 и ID2; однако, после выполнения процедуры в нашей производственной базе данных (очень занятая база данных) и наличие больше чем 6 250 записей в каждой таблице, я заметил один инцидент, где ID1 не соответствует ID2! Хотя обычно для каждой записи, вставленной в T1, существует запись, вставленная в T2, и столбец идентификационных данных в обоих последовательно увеличивается.
"Неправильные" записи были чем-то как этот:
ID1 Col1
---- ---------
4709 data-4709
4710 data-4710
ID2 ID1 Col1
---- ---- ---------
4709 4710 data-4710
4710 4709 data-4709
Отметьте "инвертированное", ID1 во второй таблице.
Зная не так очень о SQL Server под операциями, я поместил следующую "теорию", возможно, кто-то может исправить меня на этом.
То, что я думаю, - то, что, потому что цикл быстрее, чем физическая запись в таблицу и/или возможно некоторая другая вещь задержала запись, записи были буферизованы. То, когда это прибывает время, чтобы записать им, они были, записало без определенного порядка.
Это даже возможно, если не, как объяснить вышеупомянутый сценарий?
Если да, то у меня есть другой вопрос повыситься. Что, если первая вставка (из кода выше) была отложена? Разве это не означает, что я не заставлю корректные ИДЕНТИФИКАЦИОННЫЕ ДАННЫЕ вставлять во вторую таблицу? Если ответ этого также да, что я могу сделать, чтобы обеспечить, чтобы вставка в этих двух таблицах произошла в последовательности с корректными ИДЕНТИФИКАЦИОННЫМИ ДАННЫМИ?
Я ценю любой комментарий и информацию, которые помогают мне понять это.
Заранее спасибо.
6 ответов
На IDENTITY нельзя положиться. чтобы решить эту проблему для вашей второй таблицы. Если вы заботитесь о сгенерированном значении первичного ключа для этой строки, вы должны сгенерировать его.
IDENTITY - это способ сказать: «Я не хочу хлопот с генерацией ключа сам, просто сделай это за меня, и я запрошу сгенерированное значение, если и когда оно мне понадобится ».
Здесь может происходить то, что два потока одновременно вставляют строки, ни один из них еще не зафиксирован, поэтому вы получаете следующий сценарий:
Thread 1 Thread 2
get id for table 1 = 4709
get id for table 1 = 4710
insert row for table 1
insert row for table 1
get id for table 2 = 4709
get id for table 2 = 4710
insert row for table 2
insert row for table 1
У вас есть два способа решить вашу проблему:
- Удалить IDENTITY для первичного ключа во второй таблице
- Используйте
SET IDENTITY_INSERT ON, чтобы вы могли предоставить ключ для него, сохраняя настройку IDENTITY
. Однако в этом случае я бы использовал метод номер 1. Метод №. 2 обычно используется при импорте данных в пустую таблицу. Вы не хотите, чтобы база данных автоматически генерировала идентификатор, который вы позже захотите использовать самостоятельно (поскольку он берется из первой таблицы), поэтому вам следует отключить настройку IDENTITY для первичного ключа второй таблицы.
Или вы могли бы попытаться вообще не полагаться на ключ для этой таблицы, поскольку у вас есть ссылка на внешний ключ, действительно ли вам нужно, чтобы значения ключей были одинаковыми?
Исходная запись:
- Всякий раз, когда вы делаете что-то, что изменяет данные, это записывается в ЖУРНАЛ базы данных в тот момент, и вы не получите подтверждения транзакции, пока это не произойдет. Это D в условиях ACID (теория базы данных).
- Грязные страницы базы данных записываются на диск «в фоновом режиме». Если слишком много грязных, срабатывает контрольная точка, и все они выгружаются.
Итак, до письменной части.
Вы, вероятно, столкнетесь с простым фактом, что, хотя отдельные операторы являются атомарными, у занятой базы данных может быть более одного потока, выполняемого по ней. Так что, по сути, между операторами произошло переключение потока.Один поток получил Id1, другой - один приоритет, id1, id2, затем первый - id2.
Ничего особенного;) Типичное нормальное поведение базы данных при одновременном выполнении нескольких потоков. Ничего общего с письмом как таковым.
Обычно между SET @MyID = Scope_Identity () и следующим оператором приоритет может получить другой поток;)
не полагайтесь на фактические значения столбцов идентификации для логики бизнеса/приложения вы можете только предположить, что они будут уникальными!
Конечно, ваш вышеуказанный сценарий возможен - и вполне вероятен тоже.
Если у вас есть две отдельные независимые таблицы, обе из которых используются для запросов и вставок, обе с отдельным полем IDENTITY(1,1), нет абсолютно никакой гарантии , что вставка в одну таблицу, а затем во вторую будет выполнена в том же порядке!
Если необходимо установить связь между ними, вставьте идентификатор первой таблицы во вторую таблицу в качестве внешнего ключа. Вы не можете полагаться на то, что идентификаторы, сгенерированные из IDENTITY, будут одинаковыми в обеих таблицах!
Это известная ошибка в SQL Server.
Проблема в том, что при создании плана запроса распараллеливание приводит к неправильной идентификации области.
Переместите эту часть в отдельную процедуру, так что передайте параметры и верните идентификатор области - теперь это должно быть правильно.
Если я правильно помню, это проявляется только в таблицах с миллионом строк и более.
Ага, вот база знаний: http://support.microsoft.com/default.aspx?scid=kb;en-us;2019779&sd=rss&spid=2855
. избежать этой проблемы, используя функцию SQL 2005, предложение OUTPUT. Ссылка ниже.