Как получить SQL-вставку и /или обновление, чтобы не блокировать всю таблицу на MS SQL Server
Очень новичок в работе с БД, поэтому оцените ваше терпение с помощью основного вопроса. Я запускаю SQL Server 2014 на своей локальной машине, и у меня есть небольшая таблица и основное клиентское приложение для тестирования различных подходов. Я получаю то, что кажется блокировкой таблицы во время обоих INSERT INTO и UPDATE. Клиент представляет собой приложение ASP.NET со следующим кодом:
OleDbConnection cn = new OleDbConnection("Provider=SQLNCLI11; server=localhost\\SQLEXPRESS; Database=<my db>; user id=<my uid>; password=<my pwd>");
cn.Open();
OleDbTransaction tn = cn.BeginTransaction();
OleDbCommand cmd = new OleDbCommand("INSERT INTO LAYOUTSv2 (LAYOUTS_name_t, LAYOUTS_enabled_b, LAYOUTS_data_m) VALUES ('name', '-1', 'data')", cn, tn);
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT SCOPE_IDENTITY()";
int newkey = Decimal.ToInt32((decimal)cmd.ExecuteScalar());
Console.WriteLine("Created index " + newkey);
Thread.Sleep(15000);
tn.Commit();
tn = cn.BeginTransaction();
cmd.CommandText = "UDPATE LAYOUTSv2 SET LAYOUTS_enabled_b='-3' WHERE LAYOUTS_key='" + newkey + "'";
cmd.Transaction = tn;
cmd.ExecuteNonQuery();
Console.WriteLine("updated row");
Thread.Sleep(15000);
tn.Rollback();
cn.Close();
Я запускаю этот код, затем из студии управления я запускаю SELECT * FROM LAYOUTSv2. В обоих случаях, когда клиентский поток приостанавливается (то есть до фиксации /откат), запрос SELECT зависает до тех пор, пока не произойдет фиксация /откат.
В таблице указано поле LAYOUTS_key, назначенное в качестве первичного ключа. В окне свойств он показывает, что он уникален и сгруппирован, причем блокировки страниц и блокировки строк разрешены. Настройка эскалации блокировки для таблицы отключена ... Я пробовал и другие доступные настройки таблицы и AUTO без каких-либо изменений. Я пробовал SELECT ... WITH (NOLOCK) и немедленно возвращает результат, но как хорошо предупрежден here и в других местах это не то, что я должен делать. Я попытался поставить подсказку ROWLOCK как на INSERT и UPDATE, но ничего не изменилось.
Поведение, которое я ищу, это следующее: до совершения INSERT, запросы из других потоков читают все строки, кроме один из которых является INSERT ed. Перед фиксацией запросов UPDATE из других потоков прочитайте стартовую версию строки UPDATE ред. Есть ли способ сделать это? Если мне нужно предоставить другую информацию, чтобы уточнить мой вариант использования, пожалуйста, дайте мне знать. Спасибо.
2 ответа
Скорее всего, он не блокирует «целую таблицу».
Это блокировка строки в таблице, но ваш SELECT * FROM LAYOUTSv2 пытается прочитать всю таблицу, поэтому обязательно блокируется этой блокировкой.
Для случая вставки вы можете просто указать подсказку READPAST, чтобы пропустить мимо заблокированной строки, однако это не даст желаемого результата для случая UPDATE (он снова пропустит строку, не прочитав стартовую версию строки).
Если вы сконфигурируете базу данных для прочитайте выделенную блокировку моментальных снимков даст вам желаемый эффект для обоих случаев (за счет большего использования tempdb)
Операторы вставки и обновления должны создавать блокировки на уровне строк. Однако, когда число блокировок в любой транзакции составляет 5000 или более, происходит эскалация блокировки и создается блокировка уровня таблицы. См. Ниже.
https://technet.microsoft. ком /EN-US /библиотека /ms184286 (v = sql.105) .aspx