How does SQL Server handle SELECT statements in a transactional INSERT?

Question

As part of a retail closing process, there is a transactional stored procedure that selects from each of 18 tables and inserts them into a separate database for later mainframe processing. This procedure is showing some strange timing behavior, and I think it is because of a fundamental misunderstanding of the way transactions work in SQL Server.

I recognize that this isn't the best architecture for this problem, and the new solution is being developed, but in the meantime, I need to improve this process.

The stored procedure is running based on user request, and looks something like this:

BEGIN TRANSACTION

INSERT INTO Table1
        (Column1,
        Column2,
        Column3,
        Column4,
        Column5,
        Column6,
        Column7,
        Column8)
    SELECT
        Column1,
        Column2,
        Column3,
        Column4,
        Column5,
        Column6,
        Column7,
        Column8
    FROM
        OLTPTable T
    INNER JOIN
        LookupTable1 L
    ON  
        T.Foreign = L.Key

INSERT INTO Table2
        (Column1,
        Column2,
        Column3)
    SELECT
        Column1,
        Column2,
        Column3
    FROM
        OLTPTable2 T
    INNER JOIN
        LookupTable2 L
    ON  
        T.Foreign = L.Key

INSERT INTO Table3
        (Column1,
        Column2,
        Column3,
        Column4,
        Column5,
        Column6)
    SELECT
        Column1,
        Column2,
        Column3,
        Column4,
        Column5,
        Column6
    FROM
        OLTPTable3 T
    INNER JOIN
        LookupTable3 L
    ON  
        T.Foreign = L.Key

-- Through Table 18 and OLTP Table 18

COMMIT TRANSACTION

The logging looks something like this:

Table1      0.2 seconds 354 rows
Table2      7.4 seconds 35 rows
Table3      3.9 seconds 99 rows

There isn't a clear correlation between quantity of rows or complexity of joins and time.

My question is - on a long procedure like this, what is the effect of the transaction? Does is lock all the tables in the subselects at the beginning? One at a time? Is it waiting for the source table to be available for a lock, which is causing the waits?

Why are you doing an inner join and not using any of the joined data? I see nothing but inserting from OLTPtable3 into table3, yet you have joined LookupTable3. Have you not added some of the Stored Procedure to your question or is it that complete for table3? — ChrisBint, Jun 27 '11 at 16:15
Do you need some kind of isolaton/concurrency handling over 18 table INSERTs? There are other ways if you can explain more... — gbn, Jun 27 '11 at 16:43
@Chris Bint - it's just an example. The real procedure is more complex. @a1ex07 - yes @gbn - I need to take the information from the tables without a dirty read. That's what the transaction is supposedly doing. I would love to hear about other options. — Bill Sempf, Jun 27 '11 at 17:49

score 4 · Answer 1 · edited May 23 '17 at 12:04

With default READ COMMITTED isolation level, Read (shared) locks will be there for the duration of each SELECT only. Not for the transaction.

To change this, you'd need REPEATABLE_READ on higher to persist the shared (read) lock until the end of the transaction.

Notes:

the lock granularity (row, page etc) is separate to this duration
other processes will be able to read the SELECTed tables

Your INSERT durations will be affected by a whole raft of conditions. Some of the,:

indexes for SELECT
indexes to be maintained as part of the INSERT
triggers on the target table
other writing and reading processes
transaction log file writes
...and all the stuff I mentioned here: Why does an UPDATE take much longer than a SELECT?

Edit:

After more thought, would you be better with sp_getapplock etc to maintain app-level concurrency.

Didn't know about sp_getapplock. Appreciate that. – Bill Sempf Jun 27 '11 at 18:28 — Bill Sempf, Jun 27 '11 at 18:28

score 2 · Answer 2 · answered Jun 27 '11 at 16:39

2

In my opinion, using or not using transactions does not affect performance too much in this case. I suggest that you need to tune each insert individually (take into account that performance of your inserts depends not only on sub-selects, but also on inserts- the more indexes in target tables, the worse insert performance; you might also have chosen wrong clustered indexes for some tables).

answered Jun 27 '11 at 16:39

a1ex07

36,826
12
90
103

Agree: unless there is some atomic unit of work over 18 tables to be done – gbn Jun 27 '11 at 16:42
gbn: Sure, but it (atomicity) has more with business logic rather than performance. – a1ex07 Jun 27 '11 at 16:50
Yes, that is what I am starting to see. Thanks for the response. – Bill Sempf Jun 27 '11 at 18:28

score 2 · Accepted Answer · answered Jun 27 '11 at 17:01

Under what isolation level?

For the write part (the insert) everything is the same no matter the isolation level: all inserted rows are locked in X mode until the end of the end of the transaction. Row locks may escalate to table locks.

If the isolation level is left at the default read committed and read-committed-snapshot-isolation option on the database is OFF the things will happen like this: each select will lock one row at a time in S mode and will release it immediately.

Under repeatable read isolation the S locks acquired by the SELECTs will be retained until the end of the transaction, and they may escalate to table S locks.

Under serializable read isolation the SELECTs will acquire range locks instead of row locks and will retain them till until the end of the transaction. Again, lock escalation may occur.

Under snapshot isolation the SELECTs acquire no locks at all (ignoring some technicalities around schema stability locks), they will read any locked row from the versions tore instead. The version read corresponds to the snapshot of the value at the beginning of the transaction.

Under read committed isolation when read-committed-snapshot-isolation is enabled on the database the SELECTs will not acquire any locks, they will read from the version store instead if a row is locked. The version read corresponds to the snapshot of the value at the beginning of the statement.

Now back to your question, why do you see differences in performance? As with any performance question, it is best you apply a investigation methodology, and Waits and Queues is an excellent one. Once you investigate the root cause, a proper solution can be proposed.

I thought that this rabbit hole went deeper than I thought. Thanks. — Bill Sempf, Jun 27 '11 at 18:27

How does SQL Server handle SELECT statements in a transactional INSERT?

3 Answers3