August 7, 2019 at 6:13 am
Hi,
I have a stored procedure where i update table "A" with matching records from table "B". Before Update there is a "begin tran" and after several updates and inserts in table "A" there is a "Commit" Statement. Now my question is the table "B" is a live table where there will be lot of Inserts and updates continuously happening. In this case will it block the other transactions which are using table "B" ? As i am updating "A" by referencing "B" will it block "B" also????
Thanks in Advance!
August 7, 2019 at 8:09 am
it depends on lock granularity
in worst case scenario whole both tables will be locked for other sessions till the end of tran
August 7, 2019 at 9:11 am
Thank you!
August 7, 2019 at 11:32 am
Also worth noting, modifying two tables within a transaction can be a vector towards deadlocks if another procedure or query modifies the same two tables, but in a different order.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 7, 2019 at 12:34 pm
"after several updates and inserts..."
Maybe it's worth asking... does your procedure specify 'set xact_abort on'? When a proc has multiple DML statement in a transaction and without xact_abort setting can result in tricky situations. Without that setting it's possible a rollback will not be complete if the procedure throws an exception. If the proc only contains a single DML statement (unless it's 'merge') it's possibly ok to omit (although I never do). "raises a run-time error" = the old way of saying "throws an exception".
The docs say:
"When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back." "OFF is the default setting."
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
August 7, 2019 at 3:35 pm
And use try..catch
August 7, 2019 at 4:21 pm
And use try..catch
I hear a whole lot of people making that recommendation. Try/Catch is usually not necessary, especially in the presence of SET XACT_ABORT ON, which will auto-magically produce the correct ROLLBACK for the entire explicit transaction regardless of the number of statements in the explicit transaction block .
Further, most people do a really terrible job in their Catch blocks and don't even produce what just letting SQL properly handle the error does.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 7, 2019 at 4:31 pm
Not necessary, but I'd argue it's a good practice and habit to get into. It clearly sets out the transaction, which can be lost in code, and it allows you to include some logging if necessary in a catch block.
August 7, 2019 at 6:17 pm
Agree try/catch is primarily for error capture. Partial rollbacks are not common in my experience. Sql clients could cache partial form input however they do it.
In theory something could be done after the rollback inside the catch block. Here's a NoSql-ish example:
With the exception thrown the proc returns 2 rows and all the error fields. If commented out the line 'and col1_name=1/0;' the proc returns 6 rows. If the rollback is commented out in the proc, the error is: "mismatching number of BEGIN and COMMIT statements". If the 'select * from dbo.tests' is placed before the rollback it will return 4 rows. If placed after the rollback it will return 2 rows.
In words the proc says: "here's some data, put it with all the other data (if you don't already have it) and then return everything (no matter what and if something goes wrong tell me exactly what happened)."
drop table if exists dbo.test;
go
create table dbo.test(
col1_namenvarchar(1024) not null ,
col2_namenvarchar(1024) not null);
create unique nonclustered index ndx_test_unq_col1 on dbo.test(col1_name)
with
(ignore_dup_key = on);
go
insert test(col1_name, col2_name) values('this', 'that'), ('these', 'those');
insert test(col1_name, col2_name) values('this', 'that'), ('these', 'those');
drop proc if exists dbo.sptest
go
create proc dbo.sptest
as
--set nocount on;
set xact_abort on;
begin transaction
begin try
insert test(col1_name, col2_name)
select
col1_name + '_next',
col2_name + '_next'
from
test;
insert test(col1_name, col2_name)
select
col1_name + '_last',
col2_name + '_last'
from
test
where
col1_name not like '%_next'
--and col1_name=1/0;
select * from test;
commit transaction;
end try
begin catch
select
error_number() error_number,
error_severity() error_severity,
error_state() error_state,
error_line () error_line,
error_procedure() error_procedure,
error_message() error_message,
xact_state() xact_state;
--select * from test;
if xact_state()=-1
rollback transaction;
select * from test;
end catch
set xact_abort off;
--set nocount off;
go
exec sptest;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply