Deadlock - simple insert - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Deadlock - simple insert

Author

Topic

tnt2va
Starting Member

7 Posts

Posted - 2005-08-19 : 12:31:22

Hi, this is my first time posting, so please be patient with me.

I have a procedure that is causing some locking for a few seconds at a time; it is a straight insert to a table. I know what procedure is causing the locking issue (thru the trace and profiler) but I am not sure how to correct it.

Some thoughts are creating a hold table, but I am afraid I will run into the same problem. Everything was working fine up until a couple of days ago, and nothing has changed to the db or procedure.

Any insight would be appreciated.

Thanks a Bunch!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-19 : 13:34:07

Welcome aboard!

Since the problem just started with no changes to the database, has the db server had a service pack upgrade? (there have been some issues from the latest service pack for certain situations.)

Or maybe the problem was there but due to an increase in usage it only became apparent now?

Feel free to post the procedure as well as the DDL for the table and associated objects (triggers, constraints, and related tables)

Be One with the Optimizer
TG

tnt2va
Starting Member

7 Posts

Posted - 2005-08-19 : 14:09:41

We have been holding off on the latest service pak, we are still at 3a.

The procedure looks something like this:
Insert Into Table (Name, Address,City,State,ZipCode, PhoneNumber, Email, Date)
values (@Name,Address,@City,@State,@ZipCode,@PhoneNumber,Email,@Date)

A unique ID is created for each row, which is the pk, and there are no triggers, constraints, etc,

Just a straight insert from various users.

Thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-19 : 14:17:21

Are there any explicit transaction in your procedure, your insert being one of the statements withing the transaction?

Are there any other concurrent processes that reference the table? Even one that just SELECTs?

Be One with the Optimizer
TG

tnt2va
Starting Member

7 Posts

Posted - 2005-08-19 : 14:23:13

None.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-19 : 14:28:06

A single insert like you're describing should not be causing a deadlock, so there must be something else happening that you're not aware of. Can you recreate the deadlock at will?

Look through this topic about using trace, profiler, and dbcc inputbuffer to help diagnose the specific problem.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49689

Be One with the Optimizer
TG

tnt2va
Starting Member

7 Posts

Posted - 2005-08-19 : 14:38:48

Thanks for the link. I have it bookedmarked already - that is how I learned to read the trace when I executed it.

Speaking of the trace - it pinpointed the sp correctly, but it also stated in the Statement Type that is a Select in line 79, which there is no select in the sp, and no line 79.

We have the profiler on, and we are monitoring the system, but so far we have not figured out how to correct the problem, like you said before,

Or maybe the problem was there but due to an increase in usage it only became apparent now?

Thanks for all your help.

tnt2va
Starting Member

7 Posts

Posted - 2005-08-19 : 15:46:36

Quick question, can I just lock the row in the table thru the procedure that is being called? I see that is a DBCC Rowlock and With (RowLock), etc,

Thanks!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-19 : 16:07:49

well yes...but it's generally not a good idea to change the locking behavior that sql server has chosen when writing data. I think its far more likely that there is a search sp out there that takes more than a few seconds that is referencing your table. The READ will keep the table locked (blocking your insert) until its completed its READs. In that case, changing the isolation level to READ UNCOMMITTED (or using with (nolock) hints) might be appropriate and won't block your insert.

Be One with the Optimizer
TG

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2005-08-19 : 16:14:46

quote:
Originally posted by tnt2va

The procedure looks something like this:
Insert Into Table (Name, Address,City,State,ZipCode, PhoneNumber, Email, Date)
values (@Name,Address,@City,@State,@ZipCode,@PhoneNumber,Email,@Date)

Is the fact that there is no @ in front of Email in the VALUES portion of your statement simply a typo here on the site, or is that an error in your actual code?

---------------------------
EmeraldCityDomains.com

tnt2va
Starting Member

7 Posts

Posted - 2005-08-19 : 16:41:04

Sorry, that is a typo.

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-08-19 : 17:12:36

quote:
A unique ID is created for each row, which is the pk, and there are no triggers, constraints, etc,

quote:
Speaking of the trace - it pinpointed the sp correctly, but it also stated in the Statement Type that is a Select in line 79, which there is no select in the sp, and no line 79.

Are you sure there is no trigger. Each time I've heard that happened, the trigger was found. Are there other inserts/updates/deletes in procedure then one you think is the problem?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-19 : 21:20:28

tnt2va,

I have no way of knowing your experience level, so PLEASE don't be offended by this if it's too obvious for you:

To confirm that you have no constraints on the table:

exec sp_helpconstraint <yourTableName>

Check for foreign key constraints. if any exist they could affect insert performance or create concurrency issues

To confirm that there are no triggers on your table (or any table that is referenced by foreign key constraints on your table)
and to find all procedures and triggers that reference your table:


select	distinct name = convert(varchar(30),object_name(id))
	,type = 
		case
		when objectproperty(id, 'isTrigger') = 1 then 'trigger'
		when objectproperty(id, 'isProcedure') = 1 then 'procedure'
		else 'something else'
		end
from	sysComments
where	text like '%<yourTableName>%'

Let us know if you find anything...

Be One with the Optimizer
TG

tnt2va
Starting Member

7 Posts

Posted - 2005-08-22 : 15:05:18

Sorry, I have not responded sooner - First Thank you for all your input, and yes there was a trigger, which was removed and solved the problem.

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources