UPDATE - 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

General SQL Server Forums

New to SQL Server Programming

UPDATE

Author

Topic

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-11-18 : 05:23:41

Hi

I have update query if i run this below query my SPID will be SUSPENDED. It's getting block.

please help me out..


	UPDATE <table>
SET 
		FSN_Manual			=	ISNULL(@Fsn_Manual,FSN_Manual) ,
		HML_Manual			=	ISNULL(@Hml_Manual,HML_Manual),
		VED_Manual			=	ISNULL(@Ved_Manual,VED_Manual), 
		ABC_Manual			=	ISNULL(@Abc_Manual,ABC_Manual),
		SERVICE_Manual		=	ISNULL(@Service_Manual,SERVICE_Manual),
		LeadTime_Manual		=	ISNULL(@LeadTime_Manual,LeadTime_Manual),
		Margin_Manual		=	ISNULL(@Margin_Manual,Margin_Manual)
FROM <table>
WHERE [USER_ID] =  @USER_ID
AND SKU			= @SKU

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-11-18 : 07:03:05

Something is obviously holding a lock on the table.

Are you doing this update in a cursor? I'm guessing you are from all the variables.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-11-18 : 07:17:18

Hi Transact Charlie

No, we are using inside the procedure just direct update based on the values.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-11-18 : 07:21:07

Ok -- so if it's not in a loop at all? You aren't iterating through a list of values?

Then maybe:

parameter sniffing?
or
another process with an open transaction holding a lock on the table.
or
Table structure?

Does this have a cascade delete on the table or is the table a heap? (does it have a clustered index?)

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-18 : 07:23:25

Must not be the solution to your problem but there is no need for "FROM <table>" in the given example.

No, you're never too old to Yak'n'Roll if you're too young to die.

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-11-18 : 07:27:24

Hi

another process with an open transaction holding a lock on the table.
yes this one possible

or
Table structure?

Does this have a cascade delete on the table or is the table a heap? (does it have a clustered index?)
no index this table

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-11-18 : 07:29:38

Hi webfred

Yes no need but if i remove same problem retain

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-11-18 : 07:52:32

quote:

another process with an open transaction holding a lock on the table.
yes this one possible

If this is possible then you will probably have to run profiler and see what's up

quote:

Table structure?

Does this have a cascade delete on the table or is the table a heap? (does it have a clustered index?)
no index this table

Ah -- your table is a heap. Performance for updates and deletes will be terrible.

You should probably add (at least) a clustered index and, depending on queries using the table, maybe 1 or more non clustered indices.

In this case, if you have a covering index over [USER_ID] and [SKU] then your update query performance would be much improved. Better performance leads to less locking.

You should consider carefully the general use for this table and add a clustered index.

Does the table not have a primary key then? Or did you decide not to implement the primary key with the normal clustered index?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-11-18 : 08:00:30

Hi Transact Charlie

Thanks a Lot

Here i don't have primary key so i can't able to create clustered index right.

So i can go for non clustered index...is it right...

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-11-18 : 08:10:58

You can create a clustered index without a primary key and you definitely should! Without a clustered index your table is just a heap. With a clustered index the table will be organised in a nice b-tree.

You don't need to have unique values either. SQL server will do some stuff behind the scenes to build the clustered index.

Check out CREATE INDEX in your documentation or in books online.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-11-18 : 08:13:03

Also -- you might find you can't create the clustered index right now because the table is in use. It will also take a while if the table is big. Maybe you should schedule this operation for a downtime period (if you have one). Or at the least for your least busy period.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-11-18 : 08:13:07

Hi Transact Charlie

Thanks a Lot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources