Deadlock Issue during Data Flow Task-Execution

Hi, folks!

I got a serious problem with an SSIS-Import. My packages import from a foreign source into a kind of temp-table (actually it's not a temporary table, it′s just filled with data and truncated after completion of the package), do some transformations and then I got a data flow task that simply copies all the rows from the "temp" to the final table. I get the following errors (here there are two simultanious copy operations from two different "temps" into the same final table.

Error: 0xC0202009 at _temp to finaltable 5 2 1, OLE DB Destination [16]: An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Transaction (Process ID 68) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.".

Error: 0xC0209029 at _temp to finaltable 5 2 1, OLE DB Destination [16]: The "input "OLE DB Destination Input" (29)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (29)" specifies failure on error. An error occurred on the specified object of the specified component.

Error: 0xC0047022 at _temp to finaltable 5 2 1, DTS.Pipeline: The ProcessInput method on component "OLE DB Destination" (16) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

Error: 0xC0047021 at _temp to finaltable 5 2 1, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0209029.

Error: 0xC02020C4 at _temp to finaltable 5 2 1, OLE DB Source [1]: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

Error: 0xC0047038 at _temp to finaltable 5 2 1, DTS.Pipeline: The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

Error: 0xC0047021 at _temp to finaltable 5 2 1, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.

Information: 0x40043008 at _temp to finaltable 5 2 1, DTS.Pipeline: Post Execute phase is beginning.

Information: 0x402090DF at _temp to finaltable 5 2 1, OLE DB Destination [16]: The final commit for the data insertion has started.

Information: 0x402090E0 at _temp to finaltable 5 2 1, OLE DB Destination [16]: The final commit for the data insertion has ended.

Information: 0x40043009 at _temp to finaltable 5 2 1, DTS.Pipeline: Cleanup phase is beginning.

Information: 0x4004300B at _temp to finaltable 5 2 1, DTS.Pipeline: "component "OLE DB Destination" (16)" wrote 5041 rows.

Task failed: _temp to finaltable 5 2 1

Warning: 0x80019002 at KDStat_alles_412: The Execution method succeeded, but the number of errors raised (7) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

Task failed: 412

Warning: 0x80019002 at kdstat_alles_master: The Execution method succeeded, but the number of errors raised (7) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

Error: 0xC0202009 at _temp to finaltable 1 2 1, OLE DB Destination [4468]: An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Transaction (Process ID 82) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.".

Error: 0xC0209029 at _temp to finaltable 1 2 1, OLE DB Destination [4468]: The "input "OLE DB Destination Input" (4481)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (4481)" specifies failure on error. An error occurred on the specified object of the specified component.

Error: 0xC0047022 at _temp to finaltable 1 2 1, DTS.Pipeline: The ProcessInput method on component "OLE DB Destination" (4468) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

Error: 0xC0047021 at _temp to finaltable 1 2 1, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0209029.

Error: 0xC02020C4 at _temp to finaltable 1 2 1, OLE DB Source [1]: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

Error: 0xC0047038 at _temp to finaltable 1 2 1, DTS.Pipeline: The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

Error: 0xC0047021 at _temp to finaltable 1 2 1, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.

Well, it looks like those two processes simply deadlock each other. But there are 11 Simultanious Data Imports which all go fine. The Issue only occurs at those two. The structure of the packages is exactly the same everywhere.

Is it possible that a previous update-sql query hasen′t committed properly and locks the datasets?

I have seen similar behaviour. Check your OLE DB Destination and make sure Table Lock is switched off. It causes a table lock on your destination - probably for performance reasons. If you do simultaneous copy to dest table, this could be the reason.

According to BOL this is switched OFF by default, but in my experience the default is ON.

Regards,

Pipo

|||

Uuuhm, jepp...

This seems to work. Gotta make a few tests, but yes. Thanks a lot, Pipo!

|||

Hi Pipo1,

I was readong your comment to someone about the Deadlock issue during Data Flow task - Execution.

I am having a similar situation.

I am very very new to SSIS, and how do I find out about the table lock on OLEDB Destination.

Please help!!!

Thanks in tons,

Meena.

|||Meena,
Double click on the OLE DB Destination, and if using fast load, you'll have a table lock option.|||

Thank You Phil.

I got the issue resolved.

On a separate note, I am having some issues with the maintenance plans of backing up my db on sql 2005.

My Maintenance plan backs up the database fine, but the scheduled job fails every single time.

All I get is the error "The transaction log for database 'mydatabase' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases".

I am doing the following in my maintenance plan:

check db integrity

shrink db

re-organize index

update statistics

backup db

maintenance cleanup

The db uses SIMPLE recovery model.(Earlier it was set to be on FULL recovery model).

Any help is appreciated!

Thanks,

Meena.

Deadlock Issue during Data Flow Task-Execution

Hi, folks!

I got a serious problem with an SSIS-Import. My packages import from a foreign source into a kind of temp-table (actually it's not a temporary table, it′s just filled with data and truncated after completion of the package), do some transformations and then I got a data flow task that simply copies all the rows from the "temp" to the final table. I get the following errors (here there are two simultanious copy operations from two different "temps" into the same final table.

Error: 0xC0202009 at _temp to finaltable 5 2 1, OLE DB Destination [16]: An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Transaction (Process ID 68) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.".

Error: 0xC0209029 at _temp to finaltable 5 2 1, OLE DB Destination [16]: The "input "OLE DB Destination Input" (29)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (29)" specifies failure on error. An error occurred on the specified object of the specified component.

Error: 0xC0047022 at _temp to finaltable 5 2 1, DTS.Pipeline: The ProcessInput method on component "OLE DB Destination" (16) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

Error: 0xC0047021 at _temp to finaltable 5 2 1, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0209029.

Error: 0xC02020C4 at _temp to finaltable 5 2 1, OLE DB Source [1]: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

Error: 0xC0047038 at _temp to finaltable 5 2 1, DTS.Pipeline: The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

Error: 0xC0047021 at _temp to finaltable 5 2 1, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.

Information: 0x40043008 at _temp to finaltable 5 2 1, DTS.Pipeline: Post Execute phase is beginning.

Information: 0x402090DF at _temp to finaltable 5 2 1, OLE DB Destination [16]: The final commit for the data insertion has started.

Information: 0x402090E0 at _temp to finaltable 5 2 1, OLE DB Destination [16]: The final commit for the data insertion has ended.

Information: 0x40043009 at _temp to finaltable 5 2 1, DTS.Pipeline: Cleanup phase is beginning.

Information: 0x4004300B at _temp to finaltable 5 2 1, DTS.Pipeline: "component "OLE DB Destination" (16)" wrote 5041 rows.

Task failed: _temp to finaltable 5 2 1

Warning: 0x80019002 at KDStat_alles_412: The Execution method succeeded, but the number of errors raised (7) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

Task failed: 412

Warning: 0x80019002 at kdstat_alles_master: The Execution method succeeded, but the number of errors raised (7) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

Error: 0xC0202009 at _temp to finaltable 1 2 1, OLE DB Destination [4468]: An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Transaction (Process ID 82) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.".

Error: 0xC0209029 at _temp to finaltable 1 2 1, OLE DB Destination [4468]: The "input "OLE DB Destination Input" (4481)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (4481)" specifies failure on error. An error occurred on the specified object of the specified component.

Error: 0xC0047022 at _temp to finaltable 1 2 1, DTS.Pipeline: The ProcessInput method on component "OLE DB Destination" (4468) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

Error: 0xC0047021 at _temp to finaltable 1 2 1, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0209029.

Error: 0xC02020C4 at _temp to finaltable 1 2 1, OLE DB Source [1]: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

Error: 0xC0047038 at _temp to finaltable 1 2 1, DTS.Pipeline: The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

Error: 0xC0047021 at _temp to finaltable 1 2 1, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.

Well, it looks like those two processes simply deadlock each other. But there are 11 Simultanious Data Imports which all go fine. The Issue only occurs at those two. The structure of the packages is exactly the same everywhere.

Is it possible that a previous update-sql query hasen′t committed properly and locks the datasets?

I have seen similar behaviour. Check your OLE DB Destination and make sure Table Lock is switched off. It causes a table lock on your destination - probably for performance reasons. If you do simultaneous copy to dest table, this could be the reason.

According to BOL this is switched OFF by default, but in my experience the default is ON.

Regards,

Pipo

|||

Uuuhm, jepp...

This seems to work. Gotta make a few tests, but yes. Thanks a lot, Pipo!

|||

Hi Pipo1,

I was readong your comment to someone about the Deadlock issue during Data Flow task - Execution.

I am having a similar situation.

I am very very new to SSIS, and how do I find out about the table lock on OLEDB Destination.

Please help!!!

Thanks in tons,

Meena.

|||Meena,
Double click on the OLE DB Destination, and if using fast load, you'll have a table lock option.|||

Thank You Phil.

I got the issue resolved.

On a separate note, I am having some issues with the maintenance plans of backing up my db on sql 2005.

My Maintenance plan backs up the database fine, but the scheduled job fails every single time.

All I get is the error "The transaction log for database 'mydatabase' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases".

I am doing the following in my maintenance plan:

check db integrity

shrink db

re-organize index

update statistics

backup db

maintenance cleanup

The db uses SIMPLE recovery model.(Earlier it was set to be on FULL recovery model).

Any help is appreciated!

Thanks,

Meena.

DeadLock - INSERT with foreign keys

Hi,

In my database I have 2 tables - Batch & Device and a stored procedure - AddBatchDevice which accepts a XML input as follows –

And inserts one record in Batch Table and N number of records as specified in the Device table.

<BatchDevice>

<BatchGuid/>

<Size/>

<Devices>

<Device>

<HwId/>

</Device>

<Device>

<HwId/>

</Device>

</Devices>

</BatchDevice>

When I call the stored procedure from multiple instances - its getting into deadlock (KeyLock) on the INSERT Statement (for Device table).

If the foreign key constraint between Batch and Device table is deleted – the deadlock doesn’t happen but I cannot remove the foreign key constraint.

Please let me know if there are other ways to resolve this deadlock.

Steps for repro:

1) Create a test Database in the server

2) Run the code snippet Create.sql on the test database (it will create 2 tables and one stored procedure)

3) Open two query windows for the test Database and execute the command listed in code snippet deadlock.sql simultaneously.

Code Snippet - Create.sql

Code Snippet

CREATE TABLE [dbo].[Batch](
[Id] [int] IDENTITY(1,1) NOT NULL,
[BatchGuid] [uniqueidentifier] NOT NULL,
[Size] [int] NOT NULL
CONSTRAINT [PK_Batch] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY],
CONSTRAINT [ukBatchGuid_Batch] UNIQUE NONCLUSTERED
(
[BatchGuid] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Device](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[Hwid] [bigint] NOT NULL,
[BatchId] [int] NOT NULL,
CONSTRAINT [PK_Device] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
CREATE NONCLUSTERED INDEX [IX_Device_BatchID] ON [dbo].[Device]
(
[BatchId] ASC
)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

ALTER TABLE [dbo].[Device] WITH CHECK ADD CONSTRAINT [FK_Device_Batch] FOREIGN KEY([BatchId])
REFERENCES [dbo].[Batch] ([Id])
GO
ALTER TABLE [dbo].[Device] CHECK CONSTRAINT [FK_Device_Batch]
GO

CREATE PROCEDURE [dbo].[AddBatchDevice]
@.batchDeviceXML XML
AS
BEGIN

DECLARE @.batchId INT
DECLARE @.batchSize INT
DECLARE @.err INT
DECLARE @.rowCount INT
DECLARE @.itc INT

SET @.rowCount = 0
SET @.err = 1
SET @.itc = @.@.TRANCOUNT

SET NOCOUNT ON;

BEGIN TRY
IF (@.itc = 0) BEGIN TRANSACTION;

-- Populate the Batch Table

INSERT INTO dbo.Batch(BatchGuid, Size)
SELECT
BatchDetails.value('BatchGuid[1]','UNIQUEIDENTIFIER'),
BatchDetails.value('Size[1]','INT')
FROM @.batchDeviceXML.nodes('/BatchDevice') as R(BatchDetails)

SELECT @.err = @.@.ERROR, @.rowCount = @.@.ROWCOUNT, @.batchId = SCOPE_IDENTITY()

-- Check if the Batch got added.
IF (@.rowCount = 0) OR (@.err <> 0)
BEGIN
SET @.err = 13
GOTO ROLLBACKTRAN;
END

-- Retrieve Batch Size for the current Batch
SELECT @.batchSize = Size FROM dbo.Batch WHERE Id = @.batchId;

-- Populate the Device table
INSERT INTO dbo.Device (HwId, BatchId)
SELECT x.value('./HwId[1]','BIGINT') AS HwId,
@.batchId
FROM @.batchDeviceXML.nodes('/BatchDevice/Devices/Device') as R(x)

SELECT @.err = @.@.ERROR, @.rowCount = @.@.ROWCOUNT

-- Check if the BatchSize is same as number of Device Added.
IF (@.batchSize <> @.rowCount)
BEGIN
SET @.err = 6
GOTO ROLLBACKTRAN;
END

COMMITTRAN:
IF (XACT_STATE() = 1) and (@.itc = 0) COMMIT TRAN;
GOTO RETURNPOINT;

ROLLBACKTRAN:
IF (@.itc = 0) AND (@.@.TRANCOUNT > 0) and (XACT_STATE() <> 0) ROLLBACK TRAN;

RETURNPOINT:
RETURN @.err;

END TRY
BEGIN CATCH
-- rollback if the transaction is active and start from the proc
-- @.itc is the the initial transaction count when it enters the proc,
-- XACT_STATE() is zero when the transaction inactive
IF (@.itc = 0) AND (@.@.TRANCOUNT > 0) AND (XACT_STATE() <> 0)
BEGIN
ROLLBACK TRANSACTION;
END
-- Display error Message
-- EXEC [dbo].[sp_RethrowError];
PRINT ERROR_MESSAGE()

END CATCH

RETURN @.err
END
GO

Code Snippet: Deadlock

Code Snippet

DECLARE @.p1 XML
DECLARE @.guid NVARCHAR(50)

SET @.guid = CONVERT(NVARCHAR(50),newid())

SET @.p1=convert(xml,N'<BatchDevice><BatchGuid>' + @.guid + N'</BatchGuid><Size>100</Size><Devices><Device><HwId>725186448764602445</HwId></Device><Device><HwId>725133527699733597</HwId></Device><Device><HwId>725163621214188850</HwId></Device><Device><HwId>725360424970446632</HwId></Device><Device><HwId>725155285022984412</HwId></Device><Device><HwId>725344818310067191</HwId></Device><Device><HwId>725088231823400156</HwId></Device><Device><HwId>725358312743308913</HwId></Device><Device><HwId>725188965066112420</HwId></Device><Device><HwId>725236470409711434</HwId></Device><Device><HwId>725214869542733619</HwId></Device><Device><HwId>725269236196070583</HwId></Device><Device><HwId>725245337788308830</HwId></Device><Device><HwId>725286270478665392</HwId></Device><Device><HwId>725122001939204270</HwId></Device><Device><HwId>725264967238497755</HwId></Device><Device><HwId>725278832846587913</HwId></Device><Device><HwId>725320615075719837</HwId></Device><Device><HwId>725304873858777678</HwId></Device>

<Device><HwId>725137863535379341</HwId></Device><Device><HwId>725249612385940875</HwId></Device><Device><HwId>725119300331609117</HwId></Device><Device><HwId>725226526392636646</HwId></Device><Device><HwId>725216389536026547</HwId></Device><Device><HwId>725269570366518415</HwId></Device><Device><HwId>725263311873665561</HwId></Device><Device><HwId>725340378202337092</HwId></Device><Device><HwId>725313855311969064</HwId></Device><Device><HwId>725116090589146170</HwId></Device><Device><HwId>725216246506949993</HwId></Device><Device><HwId>725315130396375765</HwId></Device><Device><HwId>725209536271189293</HwId></Device><Device><HwId>725182347479939757</HwId></Device><Device><HwId>725219305400071868</HwId></Device><Device><HwId>725174388214781749</HwId></Device><Device><HwId>725255813501735845</HwId></Device><Device><HwId>725149806189360593</HwId></Device><Device><HwId>725083241757904629</HwId></Device><Device><HwId>725238516856348792</HwId></Device>

<Device><HwId>725289375597612738</HwId></Device><Device><HwId>725268638923108985</HwId></Device><Device><HwId>725251187560381100</HwId></Device><Device><HwId>725121127358656212</HwId></Device><Device><HwId>725338523561846433</HwId></Device><Device><HwId>725163562362430808</HwId></Device><Device><HwId>725161331064600595</HwId></Device><Device><HwId>725154418534216304</HwId></Device><Device><HwId>725212177384074929</HwId></Device><Device><HwId>725091809814393355</HwId></Device><Device><HwId>725223569423617615</HwId></Device><Device><HwId>725218658642332889</HwId></Device><Device><HwId>725086919050359441</HwId></Device><Device><HwId>725167692336724911</HwId></Device><Device><HwId>725203132528167587</HwId></Device><Device><HwId>725176275524886059</HwId></Device><Device><HwId>725270403041508300</HwId></Device><Device><HwId>725260454099795083</HwId></Device><Device><HwId>725086539844081549</HwId></Device><Device><HwId>725105992902748925</HwId></Device>

<Device><HwId>725251163188132078</HwId></Device><Device><HwId>725151571461604189</HwId></Device><Device><HwId>725307710183909876</HwId></Device><Device><HwId>725162260369257115</HwId></Device><Device><HwId>725144167553782168</HwId></Device><Device><HwId>725306575103488759</HwId></Device><Device><HwId>725268023454736878</HwId></Device><Device><HwId>725191445308019528</HwId></Device><Device><HwId>725145699765219874</HwId></Device><Device><HwId>725331934019785035</HwId></Device><Device><HwId>725080177396169331</HwId></Device><Device><HwId>725141959395399544</HwId></Device><Device><HwId>725338929516312679</HwId></Device><Device><HwId>725155342379274566</HwId></Device><Device><HwId>725248907355390889</HwId></Device><Device><HwId>725284235773192184</HwId></Device><Device><HwId>725319940800576294</HwId></Device><Device><HwId>725240240269421134</HwId></Device><Device><HwId>725116844602172152</HwId></Device><Device><HwId>725242591005816826</HwId></Device>

<Device><HwId>725207269552993483</HwId></Device><Device><HwId>725309585361092409</HwId></Device><Device><HwId>725347869086295885</HwId></Device><Device><HwId>725320669266522591</HwId></Device><Device><HwId>725356839312994030</HwId></Device><Device><HwId>725123424605912001</HwId></Device><Device><HwId>725230939227577006</HwId></Device><Device><HwId>725127055700922395</HwId></Device><Device><HwId>725351488560331421</HwId></Device><Device><HwId>725358800566255337</HwId></Device><Device><HwId>725307540748164922</HwId></Device><Device><HwId>725157566714134657</HwId></Device><Device><HwId>725156382030214485</HwId></Device><Device><HwId>725257373630207105</HwId></Device><Device><HwId>725352188733356044</HwId></Device><Device><HwId>725110836101340998</HwId></Device><Device><HwId>725189887487119685</HwId></Device><Device><HwId>725095111375267930</HwId></Device><Device><HwId>725117401433091911</HwId></Device><Device><HwId>725177169568742307</HwId></Device>

<Device><HwId>725330167025361822</HwId></Device></Devices></BatchDevice>')

EXEC dbo.[AddBatchDevice] @.BatchDeviceXML=@.p1

Go 1000

From looking at the code the deadlock should be coming from the non-clustered index on the device table. What I have seen in the past is that sql server will take a more agressive lock, for instance a page lock instead of a row lock. If you run a sql profile trace and capture the locks being acquired you will likely see that a higher lock is being taken. My suggestions as a quick fix is to use the with clause on the insert statement providing the rowlock directive. The syntax is INSERT INTO TableA WITH (ROWLOCK) ...

HTH.

-Chris

|||

I tried adding WITH (ROWLOCK) for the insert statement - stilll deadlocks are occuring.

Please let me know if you have any other suggestions.

Thanks,
Loonysan

|||

You need to set the isolation level to SNAPSHOT. In order to do so you must first set the ALLOW_SNAPSHOT_ISOLATION database option to ON. Here is the command to do this:

Code Snippet

ALTER DATABASE MyDBName SET ALLOW_SNAPSHOT_ISOLATION ON

Next, add this statement at the beginning of your procedure:

Code Snippet

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

I hope this solves your problem.

Best regards,

Sami Samir

|||

I looked at the execution plan of the insert statement into the device table and the foreign key lookup was doing a scan and a merge join. This was what was causing the issue. I have seen this before when the datatypes do not match, for example int to bigint, but that is not the case with your code. I believe it has something to do with the xml function that is causing the problems. I modified the code to parse the xml and put the results in a table variable and then do the insert and I did not receive any deadlocks. Let me know if you have the same results on your side. Below is the code snippet. Thanks.

-Chris

-- Populate the Device table

declare @.myTable TABLE (hwID bigint,BatchID int);

INSERT INTO @.myTable (HwId, BatchId)

SELECT x.value('./HwId[1]','BIGINT') AS HwId,

@.batchId

FROM @.batchDeviceXML.nodes('/BatchDevice/Devices/Device') as R(x)

INSERT INTO dbo.Device (HwId, BatchId)

SELECT hwID,BatchID

FROM @.myTable;

|||

Hi Chris,

Your logic works

Thanks a lot.

- Loonysan

'dbo' mapped to non-existent login - what to do? (SQL 2000)

Hello,
If I restore a database backup from a foreign system (separate SQL Server in
a separate domain) then the result of:
sp_helpuser @.name_in_db = 'dbo'
is:
UserName GroupName LoginName SID
-- -- --
dbo db_owner NULL <SID>
Hence, 'dbo' is mapped to NULL.
It causes problems if Cross DB Ownership Chaining is enabled, because dbo in
different databases is mapped to different logins.
What is the right way to correct this problem?
So far, I have performed an ad-hoc update to system table sysusers setting
the sid for 'dbo' user to the correct value:
update sysusers set sid = <SID> where name ='dbo' (<SID> retrieved from
master..syslogins table)
But I've got a feeling this is not the right way...
What should I do?
Thank you for your help!
Best regards,
AndrewHave you used sp_changedbowner before Andrew? provided the id is not a user
in the database, this will changed the dbo.
Chris Wood
"Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
news:eyKune6kGHA.1028@.TK2MSFTNGP04.phx.gbl...
> Hello,
> If I restore a database backup from a foreign system (separate SQL Server
> in
> a separate domain) then the result of:
> sp_helpuser @.name_in_db = 'dbo'
> is:
> UserName GroupName LoginName SID
> -- -- --
> dbo db_owner NULL <SID>
> Hence, 'dbo' is mapped to NULL.
> It causes problems if Cross DB Ownership Chaining is enabled, because dbo
> in
> different databases is mapped to different logins.
>
> What is the right way to correct this problem?
>
> So far, I have performed an ad-hoc update to system table sysusers setting
> the sid for 'dbo' user to the correct value:
> update sysusers set sid = <SID> where name ='dbo' (<SID> retrieved from
> master..syslogins table)
> But I've got a feeling this is not the right way...
> What should I do?
> Thank you for your help!
> Best regards,
> Andrew
>
>
>|||You're feeling is right, don't update the system tables. Use sp_changedbowner instead.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
news:eyKune6kGHA.1028@.TK2MSFTNGP04.phx.gbl...
> Hello,
> If I restore a database backup from a foreign system (separate SQL Server in
> a separate domain) then the result of:
> sp_helpuser @.name_in_db = 'dbo'
> is:
> UserName GroupName LoginName SID
> -- -- --
> dbo db_owner NULL <SID>
> Hence, 'dbo' is mapped to NULL.
> It causes problems if Cross DB Ownership Chaining is enabled, because dbo in
> different databases is mapped to different logins.
>
> What is the right way to correct this problem?
>
> So far, I have performed an ad-hoc update to system table sysusers setting
> the sid for 'dbo' user to the correct value:
> update sysusers set sid = <SID> where name ='dbo' (<SID> retrieved from
> master..syslogins table)
> But I've got a feeling this is not the right way...
> What should I do?
> Thank you for your help!
> Best regards,
> Andrew
>
>
>|||Gentlemen:
Thank you very much for your help.
The funny thing is that the owner of the database is set correctly (e.g. in
the Enterprise Manager --> database properties --> 'General' tab),
only the mapping of 'dbo' users is missing (i.e. 'dbo' is mapped to NULL).
Therefore I haven't use sp_changedbowner, but I will give it a try next
time.
Thanks a lot again!
Best regards,
Andrew
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eKvkNp6kGHA.4224@.TK2MSFTNGP05.phx.gbl...
> You're feeling is right, don't update the system tables. Use
> sp_changedbowner instead.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>|||This hopefully explains it:
The owner of a data is stored in two places. It is stored in a system table in the master database
(sysdatabases), but it is also reflected in sysusers.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
news:%23peUt66kGHA.4044@.TK2MSFTNGP03.phx.gbl...
> Gentlemen:
> Thank you very much for your help.
> The funny thing is that the owner of the database is set correctly (e.g. in
> the Enterprise Manager --> database properties --> 'General' tab),
> only the mapping of 'dbo' users is missing (i.e. 'dbo' is mapped to NULL).
> Therefore I haven't use sp_changedbowner, but I will give it a try next
> time.
> Thanks a lot again!
> Best regards,
> Andrew
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:eKvkNp6kGHA.4224@.TK2MSFTNGP05.phx.gbl...
>> You're feeling is right, don't update the system tables. Use
>> sp_changedbowner instead.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>
>