Управління транзакціями в SQL Server - важливий крок на шляху до забезпечення безперебійної роботи і спосіб уникнути помилок, пов'язаних з блокуваннями.
Гленн Беррі, Луї Девідсон і Тім Форд
В кінцевому підсумку все інструкції, що виконуються в SQL Server, є транзакційними. При виконанні навіть однієї інструкції SQL «під капотом» ініціюється неявна транзакція. Вона ініціюється і автоматично завершується. При використанні явних команд BEGIN TRAN і COMMIT TRAN можна об'єднувати їх в явні транзакції, тобто набори інструкцій, які повинні виконуватися всі або жодної.
У SQL Server реалізовані різні рівні ізоляції транзакцій для гарантії таких властивостей транзакцій, як атомарность, узгодженість, ізоляція і довговічність (ACID). На практиці це означає, що в них використовуються довго- і короткочасні блокування для забезпечення транзактного доступу до загальної бази даних і запобігання того, щоб транзакції не заважали один одному.
Взагалі кажучи, стратегія і процес дослідження та управління транзакціями SQL Server можна обмежити кількома ключовими питаннями:
- Які транзакції активні і які сеанси в них відкриті? (Адміністративні уявлення зі словами session_transactions, active_transactions)
- Які транзакції найбільше роблять велику частину роботи? (Адміністративні уявлення зі словами database_transactions)
- Які транзакції створюють проблеми з блокуваннями? (Адміністративні уявлення зі словом locks).
Моніторинг «довгограючих» транзакцій
Друге, sys.dm_tran_session_transactions, «повертає відомості про взаємозв'язки пов'язаних транзакцій і сеансів».
Лаконічне опис sys.dm_tran_database_transactions більше приховує, ніж описує справжню корисність цього подання. Наступний сценарій містить запит, який показує для кожного сеансу, які бази даних використовуються в певній транзакції, відкритої цим сеансом, чи була ця транзакція переведена в стан тільки для читання в якийсь із баз даних (за замовчуванням більшість транзакцій доступні тільки для читання) , коли це сталося, скільки записів внесено в журнал і скільки байт були задіяні від імені цих записів в журналі:
SELECT st.session_id. DB_NAME (dt.database_id) AS database_name. CASE WHEN dt.database_transaction_begin_time IS NULL THEN 'read-only'
ELSE 'read-write' END AS transaction_state. dt.database_transaction_begin_time AS read_write_start_time. dt.database_transaction_log_record_count. dt.database_transaction_log_bytes_usedFROM sys.dm_tran_session_transactions AS st INNER JOIN sys.dm_tran_database_transactions AS dt
ON st.transaction_id = dt.transaction_idORDER BY st.session_id. database_name
Такі запити уявлення sys.dm_tran_database_transactions дуже корисні для спостереження таких речей, як:
- Сеансів з відкритими транзакціями тільки для читання (це особливо важливо для «сплячих» сеансів).
- Сеансів, що призводять до неконтрольованого зростання журналу транзакцій.
- Що відбувається в «довгограючих» транзакціях (для операцій без використання неповного протоколювання одна задіяна рядок індексу створює приблизно одну запис в журналі транзакцій).
Звичайна і короткострокова блокування
У нашому прикладі сценарію використовується динамічне представлення sys.dm_tran_locks, призначене для роботи з транзакціями і описане в електронній документації так: «Повертає відомості про земельні ресурси диспетчера блокувань, активного в даний момент. Кожен рядок представляє поточний активний запит диспетчеру блокувань про блокування, яка була отримана або знаходиться в очікуванні отримання. Стовпці в результуючому наборі поділяються на дві групи: ресурс і запит. Група ресурсів описує ресурси, на які було виконано запит блокування, а група запитів описує запит блокування ».
Це адміністративне уявлення корисно для виявлення проблем з блокуваннями в екземплярі БД:
-- Look at active Lock Manager resources for current database
DB_NAME (resource_database_id) AS [Database]. resource_type. resource_subtype. request_type. request_mode. resource_description. request_mode. request_owner_type
FROM sys.dm_tran_locksWHERE request_session_id> 50 AND resource_database_id = DB_ID () AND request_session_id <> @@ SPIDORDER BY request_session_id;
-- Look for blocking
SELECT tl.resource_type. tl.resource_database_id. tl.resource_associated_entity_id. tl.request_mode. tl.request_session_id. wt.blocking_session_id. wt.wait_type. wt.wait_duration_msFROM sys.dm_tran_locks AS tl INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_addressORDER BY wait_duration_ms DESC;
Перший запит відображає перелік типів блокувань і їх стан по SPID, відібраних для однієї бази даних, причому зі списку виключені Ви зараз приєднані і системні ідентифікатори SPID. Другий запит надає інформацію про всі блокування в екземплярі. Зауважте, що другий запит підключається до подання sys.dm_os_waiting_tasks для отримання даних про тривалості очікування процесу через блокування, і через якого ресурсу.
Зазвичай для того, щоб «зловити» блокування, доводиться кілька разів виконувати кожен з цих запитів. Якщо ви виявите дві інструкції зміни даних або запит і зміна даних, які «сплелися» в жорсткій або навіть взаємної блокування, вам буде потрібно витягти текст SQL-запитів, проаналізувати їх, виконати на тестовій системі (з включеною трасуванням допомогою Profiler) і вирішити проблему шляхом зміни запитів або додавання індексів.
Гленн Беррі (Glenn Berry) працює архітектором баз даних в компанії NewsGator Technologies з міста Денвера, штат Колумбія. Він володіє званням MVP в області SQL Server, і володіє цілим набором сертифікатів Microsoft, в тому числі MCITP, MCDBA, MCSE, MCSD, MCAD і MCTS, що означає, що йому дійсно подобається здавати сертифікаційні іспити.
Луї Девідсон (Louis Davidson) працює в області ІТ вже більше 16 років в якості розробника і архітектора корпоративних баз даних. Він носив звання SQL Server MVP протягом шести років і написав чотири книги з проектування баз даних. В даний час він займається розробкою архітектури баз даних і трохи адмініструванням БД в компанії Christian Broadcasting Network, підтримуючи роботу філій в м Вірджинія-Біч, штат Вірджинія, і р Нешвілл, штат Теннесі.