Как узнать кто держит таблицу транзакцией

sys.dm_tran_current_transaction (Transact-SQL)

Возвращает строку, которая отображает сведения о состоянии транзакции в текущей сессии.

Чтобы вызвать это из Azure Synapse Analytics или analytics Platform System (PDW), используйте имя sys.dm_pdw_nodes_tran_current_transaction. Этот синтаксис не поддерживается бессерверным пулом SQL в Azure Synapse Analytics.

Синтаксис

 sys.dm_tran_current_transaction 

Возвращаемая таблица

Имя столбца	Тип данных	Description
transaction_id	bigint	Идентификатор транзакции текущего моментального снимка.
transaction_sequence_num	bigint	Порядковый номер транзакции, формирующий номер версии записи.
transaction_is_snapshot	bit	Состояние изоляции моментального снимка. Значение 1, если транзакция запускается с изоляцией моментального снимка. В противном случае — значение 0.
first_snapshot_sequence_num	bigint	Наименьший порядковый номер транзакции, которая была активна при получении моментального снимка. При выполнении транзакции моментального снимка она формирует моментальный снимок активных в этот момент транзакций. Для транзакций, не связанных с моментальными снимками, в этом столбце отображается 0.
last_transaction_sequence_num	bigint	Глобальный последовательный номер. Последний последовательный номер транзакции, созданный системой.
first_useful_sequence_num	bigint	Глобальный последовательный номер. Самый старый последовательный номер транзакции, версии строк которой должны сохраняться в хранилище версий. Версии строк, созданных предыдущими транзакциями, можно удалить.
pdw_node_id	int	Область применения: Azure Synapse Analytics, Analytics Platform System (PDW)

Разрешения

На SQL Server и управляемом экземпляре SQL необходимо разрешение VIEW SERVER STATE .

Для целей службы База данных SQL Basic, S0 и S1, а также для баз данных в эластичных пулах, учетной записи администратора сервера, учетной записи администратора Microsoft Entra или членства в ##MS_ServerStateReader## роли сервера требуется. Для всех остальных целей обслуживания базы данных SQL требуется разрешение VIEW DATABASE STATE в базе данных или членство в роли сервера ##MS_ServerStateReader## .

Разрешения для SQL Server 2022 и более поздних версий

Требуется разрешение VIEW SERVER PERFORMANCE STATE на сервере.

Примеры

Следующий пример использует тестовый сценарий, содержащий четыре параллельные транзакции, идентифицированные порядковыми номерами (XSN), который выполняется в базе данных с параметрами ALLOW_SNAPSHOT_ISOLATION и READ_COMMITTED_SNAPSHOT, установленными в значение ON. Следующие транзакции запущены:

• XSN-57 является операцией обновления с сериализуемой изоляцией.
• XSN-58 аналогична XSN-57.
• XSN-59 является операцией выбора с изоляцией моментального снимка.
• XSN-60 аналогична XSN-59.

Следующий запрос выполняется в области каждой транзакции.

SELECT transaction_id ,transaction_sequence_num ,transaction_is_snapshot ,first_snapshot_sequence_num ,last_transaction_sequence_num ,first_useful_sequence_num FROM sys.dm_tran_current_transaction; 

Результат для XSN-59.

transaction_id transaction_sequence_num transaction_is_snapshot -------------------- ------------------------ ----------------------- 9387 59 1 first_snapshot_sequence_num last_transaction_sequence_num --------------------------- ----------------------------- 57 61 first_useful_sequence_num ------------------------- 57 

Выход показывает, что XSN-59 — транзакция моментального снимка, использовавшая XSN-57 как первую активную транзакцию на момент запуска XSN-59. Это означает, что транзакция XSN-59 считывает данные, зафиксированные транзакциями с порядковыми номерами ниже чем у XSN-57.

Результат для XSN-57.

transaction_id transaction_sequence_num transaction_is_snapshot -------------------- ------------------------ ----------------------- 9295 57 0 first_snapshot_sequence_num last_transaction_sequence_num --------------------------- ----------------------------- NULL 61 first_useful_sequence_num ------------------------- 57 

Так как транзакция XSN-57 не связана с моментальными снимками, значение first_snapshot_sequence_num равно NULL .

Контроль целостности в ADB (изоляция транзакций)¶

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

В ADB используется PostgreSQL Multiversion Concurrency Control (MVCC) для управления параллельными транзакциями heap-таблиц. С MVCC каждый запрос работает со снапшотом базы данных, создаваемым в тот момент, когда запрос объявляется. Пока выполняется снапшот, запрос не может видеть сделанные другими параллельными транзакциями изменения. Это гарантирует, что запрос видит последовательное представление базы данных. Запросы, которые читают строки, никогда не блокируют транзакции, пишущие строки. И наоборот, запросы, которые записывают строки, не могут быть заблокированы транзакциями, которые читают строки. Это позволяет значительно увеличить параллелизм в сравнении с традиционными базами данных, в которых используется блокировка для координации доступа между транзакциями, которые считывают и записывают данные.

Append optimized таблицы управляются с помощью иной модели управления параллелизмом, нежели чем модель MVCC, которая обсуждалась в данном разделе. Они предназначены для приложений write-once, read-many, что никогда (или только очень редко) подразумевает обновления на уровне строк

Снапшоты¶

Модель MVCC основывается на способности системы управлять несколькими версиями строк данных. Запрос работает со снапшотом базы данных. Снапшот – это набор строк, которые видны в момент транзакции. Снапшот гарантирует, что запрос имеет действительный и корректный снимок базы данных на время выполнения запроса или транзакции.

Каждой транзакции присваивается уникальный идентификатор (XID), 32-битное значение. Оператор SQL, не являющийся частью транзакции, обрабатывается как одиночная транзакция (с добавлением BEGIN и COMMIT). Это похоже на autocommit, используемый в некоторых базах данных.

Когда транзакция вставляет строку, XID сохраняется со строкой в столбце xmin. Когда транзакция удаляет строку, XID сохраняется в столбце системы xmax. Обновление строки рассматривается как delete и insert, поэтому XID сохраняется в xmax текущей строки и xmin только что вставленного ряда. Столбцы xmin и xmax вместе со статусом завершения транзакции определяют диапазон транзакций, для которых видна текущая версия строки. Транзакция видит результаты работы всех транзакций меньше, чем xmin, но не может видеть результаты работы любой транзакции больше или равной xmax.

Операции с несколькими действиями также должны записывать, какая команда в транзакции вставляла строку (cmin) или удаляла строку (cmax), чтобы транзакция могла видеть изменения, сделанные предыдущими командами в транзакции. Последовательность команд имеет смысл только во время выполнения транзакции, поэтому она сбрасывается до 0 в самом начале.

Каждый сегмент ADB имеет свою собственную последовательность XID, которая не может сравниваться с XID других инcтансов. Мастер координирует распределенные транзакции с сегментами, использующими идентификационный номер сеанса, называемый gp_session_id. Сегменты сопоставляют идентификаторы распределенных транзакций с их локальными XID. Мастер координирует распределенные транзакции с помощью двухфазового протокола фиксации. Если транзакция не выполняется на одном из сегментов, транзакция прекращается на всех сегментах, и данные возвращаются в первоначальный вид.

Столбцы xmin, xmax, cmin и cmax для любой строки можно увидеть при помощи оператора SELECT:

SELECT xmin, xmax, cmin, cmax, * FROM tablename;

Поскольку команда SELECT выполняется на мастере, XID являются идентификаторами распределенных транзакций. В случае если команда выполняется в отдельной базе данных сегментов, значения xmin и xmax соответствуют локальным значениям XID.

Зацикливание ID Транзакций¶

Модель MVCC использует идентификаторы транзакций (XID), чтобы определить, какие строки видны в начале запроса или транзакции. XID – это 32-битное значение, поэтому база данных может теоретически выполнить более четырех миллиардов транзакций до того, как значение переполнится и обнулится. Тем не менее, в базе данных ADB используется арифметика по модулю 2^32, что позволяет XID зацикливаться. Для любого XID может быть около двух миллиардов предыдущих и новых XID. Это работает до тех пор, пока текущая версия строки примерно через два миллиарда транзакций неожиданно не станет новой строкой. Чтобы предотвратить это, ADB имеет специальный XID, называемый FrozenXID, который всегда считается старше обычного XID, с которым он сравнивается. Xmin строки должен быть заменен на FrozenXID в течение двух миллиардов транзакций, и это одна из функций, выполняемых командой VACUUM.

Очистка (vacuuming) как минимум раз в два миллиарда транзакций предотвращает зацикливание XID. База данных ADB отслеживает XID и предупреждает, когда требуется произвести очистку (операция VACUUM).

Когда значительная часть идентификаторов больше недоступна и до того, как происходит зацикливание XID, выдается предупреждение:

WARNING: database «database_name» must be vacuumed within number_of_transactions

Transactions

Если операция VACUUM не выполняется, база данных ADB перестает создавать транзакции, чтобы избежать возможной потери данных. При достижении лимита, система сообщает об ошибке:

FATAL: database is not accepting commands to avoid wraparound data loss in database

«database_name»

Параметры конфигурации сервера xid_warn_limit и xid_stop_limit управляют отображением предупреждений и ошибок. Параметр xid_warn_limit показывает количество идентификаторов транзакций перед значением xid_stop_limit. А параметр xid_stop_limit показывает количество XID перед тем, как происходит зацикливание и выдается ошибка.

Режимы изоляции транзакций¶

Стандарт SQL описывает три явления, которые могут возникать при запуске транзакций базы данных одновременно:

• Грязное чтение – явление, которое возникает, когда транзакция считывает незафиксированные данные из другой параллельной транзакции;
• Неповторяющееся чтение – ситуация, когда при повторном чтении в рамках одной транзакции ранее прочитанные данные оказываются измененными;
• Чтение фантомов – ситуация, когда при повторном чтении в рамках одной транзакции одна и та же выборка дает разные множества строк.

Стандарт SQL определяет четыре режима изоляции транзакций, которые должны поддерживать системы баз данных:

Arenadata DB реализует только два различных уровня изоляции транзакций, при этом можно запросить любой из четырех описанных уровней. Уровень READ UNCOMMITTED ведет себя как READ COMMITTED, а уровень SERIALIZABLE откатывается к REPEATABLE READ.

Оператор SQL SET TRANSACTION ISOLATION LEVEL устанавливает режим изоляции для текущей транзакции. Режим должен быть установлен перед любыми операциями SELECT, INSERT, DELETE, UPDATE или COPY:

BEGIN; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; . COMMIT; 

Режим изоляции также может быть указан как часть инструкции BEGIN:

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Режим изоляции транзакции по умолчанию можно изменить с помощью свойства default_transaction_isolation.

Удаление неиспользуемых строк из таблиц¶

Обновление или удаление строки оставляет предыдущую версию строки в таблице. Если строка с истекшим сроком действия указана в активных транзакциях, ее можно удалить, а занимаемое ею пространство повторно использовать. За это действие отвечает команда VACUUM.

Когда недействительные строки накапливаются в таблице, дисковые файлы должны быть расширены для размещения новых строк. Увеличенная нагрузка на ввод/вывод дисков, используемых для обработки запросов, негативно влияет на производительность. Эта ситуация называется раздутие (bloat), и её следует контролировать с помощью регулярной чистки.

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

VACUUM не консолидирует страницы и не уменьшает размер таблицы на диске. Пространство, которое эта команда восстанавливает, доступно только через карту свободного пространства. Чтобы размер файлов на диске не рос, важно хотя бы один раз в день запускать VACUUM. Также важно запускать VACUUM после выполнения транзакции, которая обновляет или удаляет большое количество строк.

Команда VACUUM FULL перезаписывает таблицу без неиспользуемых строк, сводя ее к минимальному размеру. Для создания новой таблицы должно быть достаточно места на диске. При этом таблица блокируется до тех пор, пока команда VACUUM FULL не завершится. Это очень ресурсоемко по сравнению с обычной командой VACUUM, и ее можно избежать или отложить путем регулярной чистки. Лучше всего запускать VACUUM FULL в течение периода технического обслуживания. Альтернативой VACUUM FULL является воссоздание таблицы с помощью инструкции CREATE TABLE AS с последующим удалением старой таблицы.

Карта свободного пространства находится в общей памяти и отслеживает свободное пространство для всех таблиц и индексов. Каждая таблица или индекс использует около 60 байт памяти, и каждая страница со свободным пространством занимает 6 байт. Два параметра конфигурации системы определяют размер карты свободного пространства max_fsm_pages и max_fsm_relations.

Данный параметр устанавливает максимальное количество дисковых страниц, которые могут быть добавлены в общую карту свободного пространства. Для каждого слота страницы потребляется 6 байт общей памяти. Значение по умолчанию — 200000. Этот параметр должен быть установлен как минимум в 16 раз больше значения max_fsm_relations.

Параметр устанавливает максимальное количество отношений, которые отслеживаются в карте свободного пространства. Этот параметр должен быть установлен больше, чем общее количество таблиц + индексов + системных таблиц. Значение по умолчанию — 1000. Для каждого отношения к каждому сегменту потребляется около 60 байт памяти. Рекомендуется устанавливать параметр на более высокое значение.

Если карта свободного пространства слишком маленькая, дисковые страницы с доступным пространством не добавляются на карту, и это пространство не может быть повторно использовано до тех пор, пока не будет запущена, по меньшей мере, следующая команда VACUUM. Это приводит к росту файлов.

Можно запустить VACUUM VERBOSE tablename, чтобы получить отчет по сегменту о количестве удаленных строк, количестве затронутых страниц и количестве страниц с полезным свободным пространством.

Чтобы узнать, сколько страниц таблица использует во всех сегментах, необходимо запросить системную таблицу pg_class. Чтобы получить точные данные обязательно следует сначала выполнить ANALYZE для таблицы.

SELECT relname, relpages, reltuples FROM pg_class WHERE relname = ‘tablename’;

Другим полезным инструментом является gp_bloat_diag в схеме gp_toolkit, который идентифицирует раздутие (bloat) в таблицах путем сравнения фактического количества используемых таблицей страниц с ожидаемым числом.

© Copyright 2023, Arenadata.io.

Как посмотреть кто держит таблицу в Oracle и убить сессию

Для определения какой пользователь держит таблицу выполняем запрос (указываем имя необходимой таблицы) :

Select MACHINE, OSUSER, MODULE from v$session where SERIAL# =(

select serial# from v$session where sid = (

select sid from v$lock where id1= (

select object_id from all_objects where object_name='ИМЯ ТАБЛИЦЫ'

) ) )

Для отключения сессии:

Сначала находим ID таблицы.

select object_id from all_objects where object_name = 'TABLE_NAME'

Затем находим ID сессии, которая блокирует эту таблицу.

select sid from v$lock where id1 = 22222 or id2 = 2222

Затем находим серийный номер сессии

select sid, serial# from v$session where sid = 134

А потом прибиваем сессию к чертям. Параметр — sid || ‘,’ || serial#

alter system kill session '134,9107' immediate

Поиск блокировок в MS SQL Server

13.10.2022

itpro

SQL Server

Один комментарий

Блокировки в SQL Server позволяют обеспечивать целостность данных при одновременном изменении несколькими пользователя. SQL Server блокирует объекты в таблице при начале транзакции и снимает блокировку при ее завершении. В этой статье мы научимся искать блокировки в базе данных MS SQL Server и удалять их.

Можно сымитировать блокировку одной из таблиц с помощью незакрытой транзакции (которая не завершена через rollback или commit). Например, выполните такой SQL запрос:

USE tesdb1
BEGIN TRANSACTION

DELETE TOP(1) FROM tblStudents

SQL Server перед внесением изменений сначала заблокирует таблицу. Попробуйте открыть SQL Server Management Studio и выполнить простой SQL запрос на выборку:

SELECT * FROM tblStudents

Запрос зависнет в состоянии ( Executing query ) пока не отвалится по таймауту. Дело в том, что запрос SELECT пытается обратиться к данным в таблице, которая заблокирована SQL Server-ом.

В SQL Server можно настроить блокировку на уровне строки или на уровне всей таблицы.

Чтобы вывести список заблокированных запросов в MSSQL Server, выполните команду:

select cmd,* from sys.sysprocesses
where blocked > 0

Либо вывести список блокировок для конкретной базы данных:
SELECT * FROM master.dbo.sysprocesses
WHERE
dbid = DB_ID(‘testdb12’) and blocked <> 0
order by blocked

В колонке Blocked указан идентификатор процесса PID процесса, который заблокировал ресурсы. Здесь же видно и время ожидания для данного запроса (waittime в милисекундах). Можно использовать это поле для поиска наиболее старых блокировок.

В некоторых случаях блокировка может быть вызвана целым деревом процессов. Чтобы найти процесс-первоисточник блокировки нужно использовать следующий запрос для по SPID до тех пор, пока не найдете процесс со значением blocked=0 (это и будет процесс источник блокировки).

select * FROM
master.dbo.sysprocesses
where 1=1
—and blocked <> 0
and spid = 59

По SPID процесса можно получить код последнего SQL запроса, выполнено в рамках данного процесса (транзакции):

Для принудительного завершения процесса и снятия блокировки, выполните команду:

Например, в моем случае это:

Если блокировки возникают постоянно, и вы хотите определить самые ресурсоемкие запросы, можно создать отдельную хранимую процедуру:

CREATE PROCEDURE PrintCurrentCode
@SPID int
AS
DECLARE @sql_handle binary(20), @stmt_start int, @stmt_end int
SELECT @sql_handle = sql_handle, @stmt_start = stmt_start/2, @stmt_end = CASE WHEN stmt_end = -1 THEN -1 ELSE stmt_end/2 END
FROM master.dbo.sysprocesses
WHERE spid = @SPID AND ecid = 0
DECLARE @line nvarchar(4000)
SET @line = (SELECT SUBSTRING([text], COALESCE(NULLIF(@stmt_start, 0), 1),
CASE @stmt_end WHEN -1 THEN DATALENGTH([text]) ELSE (@stmt_end — @stmt_start) END) FROM ::fn_get_sql(@sql_handle))
print @line

Теперь для вывод кода SQL запроса, который заблокировал таблицу, нужно указать только его SPID:
Exec PrintCurrentCode 51

Также код запроса можно получить по sql_handle процесса блокировки. Например:

select * from sys.dm_exec_sql_text (0x0100050069139B0650B35EA64702000000000000)

Для поиска блокировок в MS SQL Server можно использовать Microsoft SQL Server Management Studio. Вы можете использовать один из следующих методов:

• Щелкните правой кнопкой по северу, запустите Activity Monitor и разверните Processes. Список запросов, ожидающих освобождения ресурсов указан со статусом SUSPENDED.
• Выберите базу данных -> Reports -> All Blocking Transactions. Здесь также видно список заблокированных запросов и SPID источника блокировки.

Предыдущая статья Следующая статья