deAttach database: source

Showing posts with label source. Show all posts

Sunday, March 25, 2012

Deadlock within stored procedure - need help

Caused by: java.sql.SQLException: Transaction (Process ID 115) was
deadlocked on lock resources with another process and has been chosen
as the deadlock victim. Rerun the transaction.
at
net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnos tic(SQLDiagnostic.java:
365)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(Td sCore.java:2781)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCor e.java:2224)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(T dsCore.java:633)
at
net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL Query(JtdsStatement.java:
418)
at
net.sourceforge.jtds.jdbc.JtdsPreparedStatement.ex ecuteQuery(JtdsPreparedStatement.java:
696)
at database.Generator.next(Generator.java:39)

Here is the script that creates our stored procedure:

USE [APPLAUSE]
GO
/****** Object: StoredProcedure [dbo].[GetGeneratorValue] Script
Date: 06/12/2007 10:27:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[GetGeneratorValue]
@.genTableName varchar(50),
@.Gen_Value int = 0 OUT
AS
BEGIN
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
SELECT @.Gen_Value = GENVALUE FROM GENERATOR WHERE
GENTABLENAME=@.genTableName
UPDATE GENERATOR SET GENVALUE = @.Gen_Value+1 WHERE
GENTABLENAME=@.genTableName
COMMIT;
SET @.Gen_Value = @.Gen_Value+1
SELECT @.Gen_Value
END

This stored procedure is the ONLY place that the GENERATOR table is
being accessed. If anyone can provide any guidance on how to avoid
the deadlock errors, I would greatly appreciate it. The goal of this
stored procedure is to select the current value of the appropriate
record from the table and then increment it, ALL automically so that
there is no possibility of multiple processes getting the same IDs.On Jun 12, 9:37 am, byahne <bya...@.yahoo.comwrote:

Quote:

Originally Posted by

We just went live today with a production SQL Server 2005 database
running with our custom Java application. We are utilizing the jTDS
open source driver. We migrated our existing application which was
using InterBase over to SQL Server. To minimize the impact to our
code, we created a stored procedure which would allow us to manage our
primary key IDs (mimicing the InterBase Generator construct). Now
that we have 150+ users in the system, we get the following error
periodically:
>
Caused by: java.sql.SQLException: Transaction (Process ID 115) was
deadlocked on lock resources with another process and has been chosen
as the deadlock victim. Rerun the transaction.
at
net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnos tic(SQLDiagnostic.java:
365)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(Td sCore.java:2781)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCor e.java:2224)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(T dsCore.java:633)
at
net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL Query(JtdsStatement.java:
418)
at
net.sourceforge.jtds.jdbc.JtdsPreparedStatement.ex ecuteQuery(JtdsPreparedStatement.java:
696)
at database.Generator.next(Generator.java:39)
>
Here is the script that creates our stored procedure:
>
USE [APPLAUSE]
GO
/****** Object: StoredProcedure [dbo].[GetGeneratorValue] Script
Date: 06/12/2007 10:27:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
>
CREATE PROCEDURE [dbo].[GetGeneratorValue]
@.genTableName varchar(50),
@.Gen_Value int = 0 OUT
AS
BEGIN
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
SELECT @.Gen_Value = GENVALUE FROM GENERATOR WHERE
GENTABLENAME=@.genTableName
UPDATE GENERATOR SET GENVALUE = @.Gen_Value+1 WHERE
GENTABLENAME=@.genTableName
COMMIT;
SET @.Gen_Value = @.Gen_Value+1
SELECT @.Gen_Value
END
>
This stored procedure is the ONLY place that the GENERATOR table is
being accessed. If anyone can provide any guidance on how to avoid
the deadlock errors, I would greatly appreciate it. The goal of this
stored procedure is to select the current value of the appropriate
record from the table and then increment it, ALL automically so that
there is no possibility of multiple processes getting the same IDs.

1. Down your isolation level to REPEATABLE READ.
2. UPDATE first, then SELECT.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
UPDATE GENERATOR SET GENVALUE = GENVALUE + 1 WHERE
GENTABLENAME=@.genTableName
SELECT GENVALUE FROM GENERATOR WHERE
GENTABLENAME=@.genTableName
COMMIT;

3. Consider allocating your numbers in batches rather than one at a
time.|||Fantastic! That appears to have fixed the problem! Thank you for
your timely response.
-b|||Actually, I spoke too soon. Even using the new stored procedure we
are getting deadlock messages, but they are less periodic.

Any other words of wisdom on why this might be happening and how to
avoid it?|||byahne (byahne@.yahoo.com) writes:

Quote:

Originally Posted by

Actually, I spoke too soon. Even using the new stored procedure we
are getting deadlock messages, but they are less periodic.
>
Any other words of wisdom on why this might be happening and how to
avoid it?

Did you also rewrite the procedure as Alex suggested? Or did you just
change the isolation level? In the latter case, you should add
"WITH (UPDLOCK)" to the SELECT query.

Else what happens is that two processes both get the read-lock on
the wrong, and then no one can procede with the UPDATE. Since only
one process at a time can hold an Update lock, one them will be held
up at this point - rather than both being held up later.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||In addition to Erland's suggestion, see
http://blogs.msdn.com/sqlcat/archiv...nce-number.aspx
for tweaks to the technique Alex suggested.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"byahne" <byahne@.yahoo.comwrote in message
news:1181745584.424801.302750@.o11g2000prd.googlegr oups.com...

Quote:

Originally Posted by

Thursday, March 22, 2012

Deadlock problem? 3 way conditional split of data from one table to another never completes

I have a source table which I'm splitting 3 ways based on a column value, but the target is the same OLE DB destination table. One conditional path is to a Multi-Cast two way split to same OLE DB gestination table. The default split is to a flat file for logging unknown record types. For a test I have data for only the 3 column values I want, but I'm having trouble with the process completing. If I pre-filter the data going into the source table by one or two values I can get the process to complete even if one split is to the multicast. If I include all three data types in the source table, I get different results depending on the order in which the conditions are specified - sometimes only two split paths are executed; other times all three are executed, but in some cases only one path of the multicast split is executed. In any case, when the three source data types are used in the test, the process never competes - the pathes are in a yellow condition and never complete.

Am I creating some kind of deadlock situation by having the source data directed to the same target table via 4 splits? Any help you can provide is appreciated. Thanks.

Aren't you using a union all transformation before the destination to bring your streams back together?|||

If your situation allows it simply uncheck the destination table option "Table Lock" and it will work.

Philippe

|||That was it! Thanks.|||Did not try that. Is that the recommeded technique to use in this situation?|||

Great, just make sure that the union all task is not better appropriate.

I use the do not lock table option only on tables that I kow for sure no other process is trying to update and or insert into.

And I do this at a time of night when nothing is accessing the table. Preferably against a staging table that will replace the production table using either sp_rename or things like that.

Philippe

|||

Jeff-B wrote:
Did not try that. Is that the recommeded technique to use in this situation?

If you were previously using separate destination connectors for the same table, then yes, that would be the recommended technique.

|||

I'd agree with Phil and Philippe - a UNION ALL component is the better way to go. It will be more performant too because there is only one insertion operation.

-Jamie

|||Would this still be the case if you were using different derived fields or different source table fields for each source to populate the fields of the target table. Does the UNION ALL allow for mapping of each source to the target or does each source to the UNION ALL have to have the same set of fields?|||You can "join" disparate sources as long as they are the same data type. That's the idea of a union, just to bring data together, but not to necessarily join it. Traditionally, unions contain many NULL fields as a result.|||

Phil Brammer wrote:
You can "join" disparate sources as long as they are the same data type. That's the idea of a union, just to bring data together, but not to necessarily join it. Traditionally, unions contain many NULL fields as a result.

I also want to clarify that if your different data flows were going to the same physical table, then yes, a union all transformation is what you want. It'll work, trust me! Come back here if you have issues with it.|||Thanks Phil. I think I understand how to use this feature now. I'll experiment and see if I achieve the same result with the 4 independent paths to the same table.

Wednesday, March 21, 2012

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,