conditional split for insert or update cause dead lock on database level

Hi

I am using conditional split Checking to see if a record exists and if so update else insert. But this cause database dead lock any one has suggestion?

Thanks

Don't try and insert and update a table from the same data flow.

-Jamie

|||

My read from db is very expensive. We can't afford to read same data twice. So I use another merge join to force waiting on updating records to finish before I insert. This solve my problem. Thanks anyway.

aproaching Before:

conditional Split on newRecords and changedRecords, OLE DB Command was used to update changedRecords, OLE DB Destination was used to insert newRecords

aproaching Now:

Conditional Split on newRecords and changedRecords, OLE DB Command was used to update changedRecords,

Merge Join is used to left outer join newRecords with output of OLE DB Command ( this leave only newRecords is availible in output, but still wait for db update command finish), then

OLE DB Destination was used to insert output from merge join.

|||

Jun Fan wrote:

My read from db is very expensive. We can't afford to read same data twice. So I use another merge join to force waiting on updating records to finish before I insert. This solve my problem. Thanks anyway.

Why do you need to read data twice? Just push one of the data paths into a raw file and then insert/update/whatever that data from another dataflow.

-Jamie

|||

Thanks for sugestion. Pushing data into temp location (file or temp table) has been too slow for large amount new records. Another merge join to force wait on update finishing seems work great at this point.

Thanks again!

Jun Fan

|||

Jun Fan wrote:

Thanks for sugestion. Pushing data into temp location (file or temp table) has been too slow for large amount new records. Another merge join to force wait on update finishing seems work great at this point.

Thanks again!

Jun Fan

Have you tried raw files? They're lightning fast.

By the way, merge join does not ensure anything. It slows up one datapath, sure, but that in no way guarantees that you will prevent your locking problem.

-Jamie

|||

If performance is a concern, I'm suprised that using the OLEDB Command is OK, as it tends to be pretty slow. I have had much better success using a Conditional Split to direct new rows (Inserts) to an OLEDB Destination that writes directly to the target table, and directs the update rows to a permanent temp table. Then I use an Execute SQL Task to issue a batch Update statement after the data flow. During performance testing in the environments I work in, this has proven to be the fastest approach.

This is a pattern that many of the regular posters on this forum use very successfully.

conditional split for insert or update cause dead lock on database level

Hi

I am using conditional split Checking to see if a record exists and if so update else insert. But this cause database dead lock any one has suggestion?

Thanks

Don't try and insert and update a table from the same data flow.

-Jamie

|||

My read from db is very expensive. We can't afford to read same data twice. So I use another merge join to force waiting on updating records to finish before I insert. This solve my problem. Thanks anyway.

aproaching Before:

conditional Split on newRecords and changedRecords, OLE DB Command was used to update changedRecords, OLE DB Destination was used to insert newRecords

aproaching Now:

Conditional Split on newRecords and changedRecords, OLE DB Command was used to update changedRecords,

Merge Join is used to left outer join newRecords with output of OLE DB Command ( this leave only newRecords is availible in output, but still wait for db update command finish), then

OLE DB Destination was used to insert output from merge join.

|||

Jun Fan wrote:

My read from db is very expensive. We can't afford to read same data twice. So I use another merge join to force waiting on updating records to finish before I insert. This solve my problem. Thanks anyway.

Why do you need to read data twice? Just push one of the data paths into a raw file and then insert/update/whatever that data from another dataflow.

-Jamie

|||

Thanks for sugestion. Pushing data into temp location (file or temp table) has been too slow for large amount new records. Another merge join to force wait on update finishing seems work great at this point.

Thanks again!

Jun Fan

|||

Jun Fan wrote:

Thanks for sugestion. Pushing data into temp location (file or temp table) has been too slow for large amount new records. Another merge join to force wait on update finishing seems work great at this point.

Thanks again!

Jun Fan

Have you tried raw files? They're lightning fast.

By the way, merge join does not ensure anything. It slows up one datapath, sure, but that in no way guarantees that you will prevent your locking problem.

-Jamie

|||

If performance is a concern, I'm suprised that using the OLEDB Command is OK, as it tends to be pretty slow. I have had much better success using a Conditional Split to direct new rows (Inserts) to an OLEDB Destination that writes directly to the target table, and directs the update rows to a permanent temp table. Then I use an Execute SQL Task to issue a batch Update statement after the data flow. During performance testing in the environments I work in, this has proven to be the fastest approach.

This is a pattern that many of the regular posters on this forum use very successfully.

Concurrent SQL Tranasactions Fail

Hi all,
I am having trouble trying to get an efficient way of checking if a
value pair exists in a table, and if not inserting the value pair and
returning the new id in the identity column. The old method I have used
has been fine for some time but the table has grown to a very large
size and the current code is causing a bottleneck.
The table consists the three columns, An identity column and two
nvarchar(15) columns. Instead of replicating the nvarchar(15) columns
over the database I have placed them in a lookup table and use the ID
from the identity column to reference the nvarchar values. I do store
multiple languages in the database so an nvarchar is necessary.
I admit the current code I use below is poor, but all of the
enhancements I have tried to make have resulted in the system failing,
as multiple copies of the procedure run and two (or more) procedures
try and insert the same data into the table (there is a unique
clustered index on the two varchar columns, and the identity column is
the primary key).
As our server application is multithreaded, it is highly likely
(certain) that two or more of these procedures will be running at once
so I have to ensure data integrity whilst checking and updating. Im
just not sure that a TABLOCKX,HOLDLOCK lock hint is the best way.
CREATE PROCEDURE dbo.CreateNewContextItem
@.CONTEXT NVARCHAR(15),
@.DATAITEM NVARCHAR(15),
@.ID INTEGER OUTPUT
AS
SET NOCOUNT ON
BEGIN TRANSACTION
SET @.ID = (SELECT id FROM contextitem WITH (TABLOCKX,HOLDLOCK) WHERE
context = @.CONTEXT AND dataitem=@.DATAITEM)
IF @.ID IS NULL
BEGIN
INSERT INTO
contextitem (context,dataitem)
VALUES
(@.CONTEXT,@.DATAITEM)
IF @.@.ERROR<>0
GOTO Errhandler
SET @.ID = Scope_Identity()
IF @.@.ERROR<>0
GOTO Errhandler
END
COMMIT TRANSACTION
RETURN 0
Errhandler:
ROLLBACK TRANSACTION
RETURN 50063 -- application specific error code
GO
Is there a way of speeding up this procedure whilst still allowing
multiple copies of it to be run at once safely? Any help would be
greatly appreciated.
Thanks in advance.How about UPDLOCK instead of the TABLOCKX hint?
ML
http://milambda.blogspot.com/|||That seems to have worked a treat!
Thanks for your help ML