A long-running ETL transaction (e.g. a large MERGE or DELETE with millions of rows) holds exclusive locks on a table while a second pipeline activity tries to read or write the same table.. The application or connection string has SET LOCK_TIMEOUT set to a low value (e.g. 5000 ms) that is too short for normal query durations under concurrent load.. An uncommitted transaction from a failed or abandoned session holds locks indefinitely — common after an ADF activity times out but the SQL session stays open due to connection pooling.. Index rebuild or reorganize operations (ALTER INDEX ... REBUILD) take schema modification locks that block all concurrent queries on the table for the duration of the operation.. Escalation from row-level to table-level locks during a large scan causes a wider blocking scope than expected — queries that previously coexisted now conflict.

Step 1: Identify the blocking chain — run: SELECT r.session_id, r.blocking_session_id, r.wait_type, r.wait_time/1000 AS wait_sec, DB_NAME(r.database_id) AS db, t.text AS sql_text FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t WHERE r.blocking_session_id > 0; — this shows every blocked session, how long it has waited, and the SQL text of the blocked query.. Step 2: Find the head blocker (the root session that is not itself blocked) — run: SELECT s.session_id, s.login_name, s.host_name, s.program_name, t.text AS last_sql FROM sys.dm_exec_sessions s CROSS APPLY sys.dm_exec_sql_text(s.most_recent_sql_handle) t WHERE s.session_id IN (SELECT blocking_session_id FROM sys.dm_exec_requests WHERE blocking_session_id > 0) AND s.session_id NOT IN (SELECT session_id FROM sys.dm_exec_requests WHERE blocking_session_id > 0);. Step 3: If the head blocker is safe to terminate (not a critical production transaction), kill it: KILL ; — then verify the blocked sessions resume by re-running the query from Step 1.. Step 4: For recurring blocking during ETL windows, add LOCK_TIMEOUT to the specific batch that needs it: SET LOCK_TIMEOUT 30000; — this gives 30 seconds instead of the default infinite wait. Do not set this globally on the server.. Step 5: Reduce lock duration by breaking large transactions into smaller batches — e.g. instead of DELETE FROM staging WHERE load_date < '2026-01-01', delete in chunks of 10,000 rows using a WHILE loop with TOP(10000) and COMMIT after each batch.. Step 6: Move index maintenance to off-peak hours and use ONLINE = ON where possible: ALTER INDEX idx_name ON dbo.table_name REBUILD WITH (ONLINE = ON); — this avoids schema modification locks during business hours.. Step 7: Enable Read Committed Snapshot Isolation (RCSI) if readers are being blocked by writers: ALTER DATABASE YourDB SET READ_COMMITTED_SNAPSHOT ON; — this lets SELECT queries read a consistent snapshot without acquiring shared locks. Requires tempdb capacity for version store.

Medium severitytimeout

SQL Server Error:
1222

Q: What is the default lock timeout in SQL Server?

By default, SET LOCK_TIMEOUT is -1, meaning the session waits indefinitely for a lock. Applications or connection strings that set a positive value (e.g. 5000 ms) will raise error 1222 after that duration expires. Check the current session setting with: SELECT @@LOCK_TIMEOUT;

Q: Is error 1222 the same as a deadlock (error 1205)?

No. A deadlock (1205) is a cycle where two or more sessions block each other and SQL Server picks a victim to roll back. Error 1222 is a one-directional timeout: session A waits for session B to release a lock, and A gives up after LOCK_TIMEOUT expires. Session B is unaware and unaffected.

Q: Should I retry after getting error 1222?

A single retry with a short delay (5-10 seconds) is reasonable for transient blocking. But if 1222 keeps recurring, retrying just delays failure — find the head blocker with sys.dm_exec_requests and fix the root cause. In ADF, configure a retry policy of 1-2 attempts with 30-second intervals on the activity level, not on the pipeline level.

Q: How do I prevent error 1222 without increasing the timeout?

Three options: (1) enable Read Committed Snapshot Isolation (RCSI) so readers do not take shared locks: ALTER DATABASE YourDB SET READ_COMMITTED_SNAPSHOT ON; (2) schedule conflicting ETL jobs sequentially instead of in parallel; (3) break large write transactions into smaller batches to reduce lock hold time.

Impact

When error 1222 repeats on a schedule (e.g. every morning during the ETL window) and causes ADF pipelines or Power BI refreshes to fail consistently — downstream tables go stale, reports show yesterday's data, and SLA breaches start stacking up.

When a pipeline activity hits error 1222, the activity fails and — depending on retry configuration — may retry 1-2 times before the pipeline is marked as failed. Downstream activities that depend on the blocked table receive stale or missing data. In ADF, dependent pipelines triggered by tumbling window or schedule triggers queue up but do not execute until the upstream pipeline succeeds. Power BI datasets refreshing from the same source may also fail, leaving reports showing outdated numbers. If the blocking persists across multiple refresh cycles, SLA violations compound as the data gap widens.

What does this error mean?

SQL Server raises error 1222 when a session requests a lock on a row, page, or table, but another session already holds an incompatible lock and the configured LOCK_TIMEOUT expires before the lock becomes available. In a data-pipeline context this typically surfaces when an ADF Copy Activity, a dbt model run, or a Power BI dataset refresh tries to read or write a table that is locked by a concurrent ETL process, a long-running stored procedure, or an open transaction left behind by a failed job. The engineer sees the activity fail with 'Lock request time out period exceeded' in the ADF monitor, the SQL Server error log, or the application's connection driver output.

Common causes

1A long-running ETL transaction (e.g. a large MERGE or DELETE with millions of rows) holds exclusive locks on a table while a second pipeline activity tries to read or write the same table.
2The application or connection string has SET LOCK_TIMEOUT set to a low value (e.g. 5000 ms) that is too short for normal query durations under concurrent load.
3An uncommitted transaction from a failed or abandoned session holds locks indefinitely — common after an ADF activity times out but the SQL session stays open due to connection pooling.
4Index rebuild or reorganize operations (ALTER INDEX ... REBUILD) take schema modification locks that block all concurrent queries on the table for the duration of the operation.
5Escalation from row-level to table-level locks during a large scan causes a wider blocking scope than expected — queries that previously coexisted now conflict.

How to fix it

1Step 1: Identify the blocking chain — run: SELECT r.session_id, r.blocking_session_id, r.wait_type, r.wait_time/1000 AS wait_sec, DB_NAME(r.database_id) AS db, t.text AS sql_text FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t WHERE r.blocking_session_id > 0; — this shows every blocked session, how long it has waited, and the SQL text of the blocked query.
2Step 2: Find the head blocker (the root session that is not itself blocked) — run: SELECT s.session_id, s.login_name, s.host_name, s.program_name, t.text AS last_sql FROM sys.dm_exec_sessions s CROSS APPLY sys.dm_exec_sql_text(s.most_recent_sql_handle) t WHERE s.session_id IN (SELECT blocking_session_id FROM sys.dm_exec_requests WHERE blocking_session_id > 0) AND s.session_id NOT IN (SELECT session_id FROM sys.dm_exec_requests WHERE blocking_session_id > 0);
3Step 3: If the head blocker is safe to terminate (not a critical production transaction), kill it: KILL <session_id>; — then verify the blocked sessions resume by re-running the query from Step 1.
4Step 4: For recurring blocking during ETL windows, add LOCK_TIMEOUT to the specific batch that needs it: SET LOCK_TIMEOUT 30000; — this gives 30 seconds instead of the default infinite wait. Do not set this globally on the server.
5Step 5: Reduce lock duration by breaking large transactions into smaller batches — e.g. instead of DELETE FROM staging WHERE load_date < '2026-01-01', delete in chunks of 10,000 rows using a WHILE loop with TOP(10000) and COMMIT after each batch.
6Step 6: Move index maintenance to off-peak hours and use ONLINE = ON where possible: ALTER INDEX idx_name ON dbo.table_name REBUILD WITH (ONLINE = ON); — this avoids schema modification locks during business hours.
7Step 7: Enable Read Committed Snapshot Isolation (RCSI) if readers are being blocked by writers: ALTER DATABASE YourDB SET READ_COMMITTED_SNAPSHOT ON; — this lets SELECT queries read a consistent snapshot without acquiring shared locks. Requires tempdb capacity for version store.

Example log output

Msg 1222, Level 16, State 56, Line 14
Lock request time out period exceeded.
The statement has been terminated.

Frequently asked questions

What is the default lock timeout in SQL Server?

By default, SET LOCK_TIMEOUT is -1, meaning the session waits indefinitely for a lock. Applications or connection strings that set a positive value (e.g. 5000 ms) will raise error 1222 after that duration expires. Check the current session setting with: SELECT @@LOCK_TIMEOUT;

Is error 1222 the same as a deadlock (error 1205)?

No. A deadlock (1205) is a cycle where two or more sessions block each other and SQL Server picks a victim to roll back. Error 1222 is a one-directional timeout: session A waits for session B to release a lock, and A gives up after LOCK_TIMEOUT expires. Session B is unaware and unaffected.

Should I retry after getting error 1222?