Історії про дані: випадок з нестандартними індексами. Продовження

Відео: Відкритий Космос / Open Space. 4 Серія. Документальний фільм. StarMedia. Babich-Design

нестандартний індекс

Відео: 4. Бази даних. Транзакції. Тригери і процедури | Технострим

Часом фахівцям программотехнических компаній буває дуже складно відтворювати проблемні ситуації, що виникають на сайтах клієнтів, але в тих випадках, коли клієнти вносять зміни в бази даних, це завдання стає ще більш складною.

В даному випадку один з покупців продуктів мого клієнта (программотехнических компанія) постійно скаржився на взаимоблокировки. Я вивів графіки взаімоблокіровок і виявив, що одним з «дійових осіб» практично у всіх критичних ситуаціях був якийсь індекс, створений фахівцями клієнта для підвищення продуктивності одного зі звітів. Цей індекс не поставлявся в якості компонента програми.

Мені здалося дуже дивним, що всякий раз, коли виникала взаімоблокіровка, надходив запит на ексклюзивну блокування на рівні таблиці (див. Екран нижче).

Відео: Заборонені та Шокуючі Факти про Pokemon GO (Покемон Го)

Чому запитується ексклюзивна блокування таблиці в разі поновлення одного рядка цієї таблиці?

З урахуванням індексу, створеного клієнтом, я розраховував зіткнутися з ексклюзивною блокуванням на рівні ключа і на більш високих рівнях виявити лише намір здійснити ексклюзивні блокування. Майже у всіх випадках виникнення взаімоблокіровок SQL Server намагався отримати ексклюзивну блокування на рівні таблиці.

Поряд з цим мене зацікавило те обставина, що SQL Server не намагався перетворити блокування ключа в блокування таблиці. Система намагалася отримати блокування таблиці безпосередньо. При наявності наявного коду ситуація не повинна була приймати такий оборот.

Присвятивши якийсь час дослідження властивостей таблиці та індексів, я зрозумів, в чому полягає проблема (див. Екран нижче).

Відео: Відповіді на Питання. випуск №2

Зверніть увагу, що після того, як фахівці клієнта створили індекс, який вони вважали тривіальним, а ми виключили можливість того, що цей індекс викличе серйозні проблеми, програмісти змогли вибрати настройки, що виключають блокування як рядків, так і сторінок індексу.

Блокування можуть ескаліровать - передаватися на вищий рівень. За замовчуванням, як тільки ви отримаєте 5000 блокувань по ключу, SQL Server прийме рішення отримати блокування більш високого рівня (здійснити ескалацію блокування), щоб звести до мінімуму непродуктивні витрати на управління блокуваннями. У версіях до SQL Server 2008 ескалація здійснювалася до рівня таблиці, а в SQL Server 2008 і пізніших версіях ескалація проводиться до рівня розділу. Такий тип ескалації можливий тільки у випадках використання безлічі блокувань по ключу, так що в даній ситуації він непридатний.

До появи версії SQL Server 7 в системі SQL Server застосовувалася блокування сторінок. Налаштування індексу allow page locks дає нам можливість моделювати дії старих версій у випадках, коли така реакція важлива для нормальної роботи програми. Майже у всіх ситуаціях ми вважаємо за краще не діяти за шаблоном старих версій, а здійснювати блокування рядків, тому настройка allow row locks повинна бути активована.

При відключенні функцій блокування рядків і сторінок система переходить до більш високого рівня деталізації. В даному випадку мова йде про рівень таблиць. Збереження ексклюзивних блокувань на рівні таблиць ускладнює здійснення паралелізму і в даній ситуації веде до виникнення великої кількості взаімоблокіровок. Як я зазначав раніше, перше правило, яким ми повинні керуватися в процесі обмеження числа взаімоблокіровок, полягає в наступному: зберігайте мінімальне число блокувань протягом мінімальних проміжків часу.

Повинен зізнатися: мені довелося неабияк поламати голову, розмірковуючи над тим, в яких випадках вибір значення false для цих властивостей має сенс. Вважаю, що ситуації, в яких слід присвоювати значення false будь-якого з цих властивостей, виникають рідко, і ми можемо без праці перевірити, чи застосовна ця можливість до індексів наших систем:

SELECT t.name AS TableName, i.name AS IndexNameFROM sys.indexes AS iINNER JOIN sys.tables AS tON i.object_id = t.object_idWHERE t.is_ms_shipped = 0AND i.allow_row_locks = 0OR i.allow_page_locks = 0-

Таким чином, немає нічого дивного в тому, що, змінивши властивості індексу, я усунув проблему взаімоблокіровок.

CREATE DATABASE IndexText-GOUSE IndexText-GOCREATE SEQUENCE dbo.CustomerIDsAS intSTART WITH 1-GOCREATE TABLE dbo.Customers (CustomerID int NOT NULLCONSTRAINT PK_dbo_Customers PRIMARY KEYCONSTRAINT DF_dbo_Customers_CustomerIDDEFAULT (NEXT VALUE FOR dbo.CustomerIDs), CustomerName nvarchar (100) NOT NULL, PrimaryContact nvarchar ( 50) NOT NULL, PhoneNumber nvarchar (20) NOT NULL, IsReseller bit NOT NULL, CreatedWhen datetime NOT NULLCONSTRAINT DF_dbo_Customers_CreatedWhen DEFAULT (SYSDATETIME ()), LastUpdated datetime NOT NULLCONSTRAINT DF_dbo_Customers_LastUpdated DEFAULT (SYSDATETIME ())) - GOINSERT dbo.Customers (CustomerName, PrimaryContact, PhoneNumber, IsReseller) VALUES (N`Big Time Movie Productions `, N`Sandra Bullock`, N`02 9552-4232 `, 1), (N`Even Bigger Movies`, N`Tom Hanks `, N`02 9234-2343 `, 1), (N`Yet Another Production Company`, N`Justin Bieber `, N`03 8283-2323`, 0), (N`A Tiny Production Company `, N`John Nobody`, N `07 2342-2342 `, 0) -GO

---