0

Why do I encounter IS(intent shared) lock on source table when I insert into target table using SSIS data flow task and S(shared) lock for same task when using T-SQL statement?

I am trying to insert around 10M rows. Concurrent inserts on the source happens all the time. So, according to lock compatibility matrix, SSIS data flow task does not block parallel inserts, but T-SQL insert does.

Why is that?

0

2 Answers 2

1

When you use SSIS, the target and source are separate operations. As far as SQL Server is concerned, it sees one connection performing a simple SELECT and another connection performing a bulk load or regular INSERT.

The simple SELECT can benefit from a locking optimization where row-level shared locks are skipped when safe to do so. This leaves only the intent-shared locks at the page level.

When the insert and select are combined in the same T-SQL statement, a different execution plan is produced, and the specific locking optimization is not applied.

If you wish to produce the same non-blocking behaviour on the source table when using INSERT...SELECT, you would need to use a row-versioning isolation level.

Read uncommitted isolation would also not take shared locks, but offers very few consistency guarantees, and might even throw an error when the data structures underneath the scan are modified (error 601).

0
0

You have come across with a lock escalation. Every time you have more than 5000 row or page locks MS SQL Server tries to escalate them to table (or partition) locks. It happens because each of the locks needs 96 bytes of memory.

And yes, the huge number of rows in select also cause lock escalation on the table you are selecting from. But it can be much more than 5000 rows because of using page locks.

I suggest you read a clean enough article about the lock escalation:

Lock Escalations

There are several ways to avoid the lock escalation:

  1. Use "short" transactions with numbers of row/page locks less than 5000.
  2. Disable it for a particular table (Since SQL Server 2008).
  3. To use the trace flag 1221 which disables lock escalation based on memory pressure, or based on number of locks.
  4. To use the trace flag 1224 which disables lock escalation based on the number of locks.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.