March 17, 2008 at 7:26 am
I do this now in a proc and I haven't noticed any problems but when I started thinking about the logic behind it I wondered if it would cause any problems.
Insert values into a table then updating the same table before committing the transaction.
Begin Tran
Insert Into t_Directory (Company) Values (@Company)
SET @NewCompanyID = @@Identity
IF isnull(@NewCompanyId,0) = 0 BEGIN ROLLBACK TRAN RETURN END
IF @NewCompanyId > 0
BEGIN
Insert Into t_Contacts (NameFirst,NameLast) Values
(@NameFirst,@NameLast)
SET @NewContactID = @@Identity
IF isnull(@NewContactId,0) = 0 BEGIN ROLLBACK TRAN RETURN END
IF @NewContactID > 0
BEGIN
Update t_Directory SET KeyContact = @NewContactID
SET @RowCount = @@Rowcount
IF isnull(@RowCount,0) = 0 BEGIN ROLLBACK TRAN RETURN END
IF @Rowcount > 0 BEGIN Commit TRAN END
March 17, 2008 at 2:37 pm
A couple of things. I would think that it would cause a deadlock since the insert is taking an exclusive lock which should block the update. The second thing I notice is that there is not a where clause on your update statement so you are updating every row in the table. Also you need to be careful using @@Identity as it returns the last identity column created, meaning if you have a trigger it will get the identity value from the trigger. Scope_Identity() is a safer function. Scope_Identity is available beginning in SQL 2000
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 17, 2008 at 3:05 pm
I don't think that this will cause a deadlock. The SP will already have the rows locked and the update will happen to the new data page. I do agree that you should be using the scope_identity function in place of @@identity and what Jack has said about there being a missing WHERE clause is also accurate. I think there may be a clearer way to write this, but it is hard to tell since you've only given us part of your SP. Feel free to post the entire procedure if you want help.
March 17, 2008 at 3:12 pm
But wouldn't the insert have an exclusive lock on the new row created until the end of the transaction and the update, without a where clause would need an exclusive table lock. I would think that this would cause a deadlock. I do admit that I am not an expert on locking so I would like to hear and explanation on how this locking would take place.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 17, 2008 at 3:28 pm
Jack Corbett (3/17/2008)
But wouldn't the insert have an exclusive lock on the new row created until the end of the transaction and the update, without a where clause would need an exclusive table lock. I would think that this would cause a deadlock. I do admit that I am not an expert on locking so I would like to hear and explanation on how this locking would take place.
If this is the same transaction that inserted it, since it has the lock on the record, no - I wouldn't think it would block itself and deadlock. The transactional lock is to prevent OTHER transactions from accessing it.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 18, 2008 at 6:40 am
Thanks for the replies.
Yeah I noticed the missing WHERE Clause in the update statement.
I've used similiar code and it hasn't generated any deadlocks that I was aware of. If it was deadlocking there would be some major issues. When I started thinking about it I wondered if it was the ideal way to do it and decided to post.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply