can i use set xact_abort on, in place of begin try cashe to rollback, in reporting stored proc becasue they do not insert update in database.

Question

can i use set xact_abort on, in place of begin try cashe to rollback, in reporting stored proc becasue they do not insert update in database.

rajemessage 14195

Hall of Fame

Points: 3299
More actions
May 28, 2016 at 2:48 am

#319047

hi,
due to some reason my report queries are blocking the entry ?
so i decided to put following ( read uncommitted/snapshot , i could have put (nolock in each tables also) in reporting stored procs)
becuase user can refresh ( situation is such)
create proc abc(@xyz int)
as
begin
set xact_abort on;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRAN
-- report stmt
commit tran;
set xact_abort off;
end
q1) So my question is, should i use transaction in report sp in above way, or should i write begin try end cache block to rollback with above query .
basically i wanted to save one begin try cache block per stored proc. So i used the above query.
other thing is reporting stored proces do not insert updagt delete in database they only do it in #temp table for reporting.
Q2) can i remove set xact_abort off;
Q2) Can i do any improvement to above query
yours sincerley

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

since you have already set the isolation level to read uncommitted, the only thing a SELECT statement will do (assuming "-- report stmt" is really a select)

is to take out a schema lock to prevent the table(s) from being modified DDL-wise until the proc completes.

it's more likely that other items are competing for the same table resources.

if the queries are poorly written or inefficient as far as indexes go, they might take a long time; i'd say a round of query tuning is much more relevant. a try-catch for a select statement would only be useful of the tables or columns are disappearing and reappearing. if hte tables existance is static, a select statement wouldn't really need a try-catch.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

rajemessage 14195 Hall of Fame Points: 3299 More actions · Answer 2

q2) is not answered. will it do any good by removing or deleting.

Lowell SSC Guru Points: 323504 More actions · Answer 3

rajemessage 14195 (6/1/2016)
q2) is not answered. will it do any good by removing or deleting.

if the only thing the query is doing is a SELECT statement, neither the XACT_ABORT nor the BEGIN TRAN are needed.

if it is doing an insert/update/delete that was not mentioned in the example , i would remove SET XACT_ABORT OFF regardless, but leave the transaction and the set xact_abort on.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

rajemessage 14195 Hall of Fame Points: 3299 More actions · Answer 4

no body is answering.

one more question

Q1 "set xact_abort no" should be the first stmt or set isolation level

that means

should i use like so.

set xact_abort no

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

or

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

set xact_abort no

Q2) should i use rollback tran in place of commit in reporting.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

BEGIN TRAN

-- Reporting stmt

rollback tran

yours sincerley