New answers tagged deadlock
1
You could use a try ... catch block:
-- Create test table
if object_id('YourTable') is not null
drop table YourTable
create table YourTable (id int identity);
-- Safeguarded insert
set identity_insert YourTable on;
begin try
insert YourTable (id) values (100);
-- this generates an exception
declare @oops int = cast('a' as int)
end try
...
1
I believe your best bet is going to be to use a TRY-CATCH. I've done two things here. First declare and get your "oldID" before your transaction begins. This will help to keep your transaction time down and avoid deadlocks as much as possible. Second I've put the "core" of the rest of it into a TRY block and then in the CATCH block put the reseed again. ...
2
Deadlocking by SELECTs can be done in a variety of ways. I have written posts about them
You can have SELECTs get deadlocked by UPDATEs and DELETEs
Are InnoDB Deadlocks exclusive to INSERT/UPDATE/DELETE?
You can have UPDATEs and DELETEs blocked by SELECTs
How are DB locks tied to connections and sessions?
Is Oracle DB immune to the InnoDB deadlocks ...
0
Why don't you put the BEGIN TRAN statement after the conditional statement has evaluated to true ?
There's no need to keep the transaction open for the check.
Try this, it might improve your locks a little :
IF NOT EXISTS (SELECT TOP 1 * FROM Table1 WITH (NOLOCK) WHERE Col1 = Val1 AND Col2 = Val2)
BEGIN
BEGIN TRAN
INSERT INTO Table1 (ID, Col1, ...
Top 50 recent answers are included
