October 23, 2015 at 1:54 pm
I'm investigating a poorly performing procedure that I have never seen before. The procedure sets the transaction isolation level, and I suspect it might be doing so incorrectly, but I can't be sure. I'm pasting a bastardized version of the proc below, with all the names changed and the SQL mucked up enough to get through the corporate web filters.
The transaction isolation level is set, but there is no explicit transaction. Am I right that there are two implicit transactions in this procedure and each of them uses snapshot isolation?
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
DECLARE @l_some_type varchar(20),
@some_type_code varchar(3),
@error int,
@error_msg varchar(50);
SELECT @l_some_type = @some_type;
SELECT @some_type_code = some_type_code
FROM dbo.some_type
WHERE some_type_description = @l_some_type;
IF @@ROWCOUNT = 0
BEGIN
SELECTx @error = -1,
@error_msg = 'Invalid some type';
GOTO Done;
END;
SELECTx avalue,
anothervalue
FROM dbo.some_table
WHERE some_type_code = @some_type_code;
SET @error = @@ERROR;
Done:
IF @error_msg > ''
RAISERROR(@error_msg, 16, 1);
RETURN @error;
October 23, 2015 at 2:13 pm
In my understanding, which could very well be wrong, the answer is no. Implicit transaction has a very specific meaning which does not necessarily apply here. Since transaction handling is not set to the best of our knowledge, it should be using the default autocommit (not implicit) transactions, in which case there would be two autocommit transactions. On the other hand, if IMPLICIT_TRANSACTIONS has been set somewhere else, then there is only ONE implicit transaction, because no COMMIT or ROLLBACK has been issued.
You can read more about transaction handling at Transaction Statements.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 25, 2015 at 5:05 pm
Hi Stephanie,
The scope of the transaction isolation levels is Session.
This means that all transactions within the session (explicit, implicit or auto-commit) will follow, in this example the optimistic locking methodology (assuming that there are no other changes to the isolation levels within the same session e.g the code that calls the sp has different isolation level that the called sp ..etc)
There are two auto-commit type transactions in the example. The transactions read data from the data-store following the SNAPSHOT isolation level rules.
Note:
The implicit transactions are set up per session too (SET IMPLICIT_TRANSACTIONS ON). The setting changes the behavior of the transactions and you'd probably investigate that too..
D.Mincic
😀
MCTS Sql Server 2008, Database Development
October 25, 2015 at 8:31 pm
This entire query uses just the snapshot isolation, The transaction would come into play only when there was any DML DDL in the query , since the query is performing a select, SQL will acquire shared locks on the pages as they are being read , however shared locks can still prevent writers from accessing the page at the time its being read. This is avoided by reading from a snapshot, hence the isolation level.
When not specified SQL always does auto commit and implicit transactions, the user can add their own transactions to manage the flow of control for example roll back the order if the orderline failed to insert. This behavior is independent of isolation levels.
October 26, 2015 at 8:05 am
Thank you to those who responded.
So it looks like the two select queries do not have to be in the same transaction. The first select statement is reading from a reference table with 5 never-changing rows. The second query is reading from a table with around 1 million rows. The query plan is a clustered index seek.
There is another procedure that runs periodically that truncates the 1 million-row table and reloads it. The other procedure inserts the table in one transaction from a user-defined table type. The UDTT does not have any indexes defined on it (I would think a primary key matching the table's clustered index would be helpful here). The other procedure does not set the transaction isolation level, so I assume it remains at Read Committed.
Both the select procedure (runs several times a minute) and the re-load procedure (runs every 10 minutes) take too long to run. The select procedure takes 0.1 - 58 seconds and the re-load procedure takes 10-46 seconds. The re-load is run by a SQL Agent job so it doesn't have a client timeout, but the select does.
I am trying to determine if there is a "smoking gun" in the SQL that explains why the select procedure can take up to 58 seconds. I have limited access to the production database itself, so I can't examine it closely. I can request various statistics once I know what I'm looking for.
I found this post that might explain the problem. From the SQL trace file I've been given, I can't even tell if the select procedure is returning errors, but it looks like it might be.
http://dba.stackexchange.com/questions/66683/transaction-isolation-level-snapshot-vs-truncate
Any further insights would be appreciated.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply