Deadlocks (I think)

Hi folks,

I have an application built on top of a questionable DB design which requires overcomplicated selects. The application is experiencing deadlocks regularly, in some cases with only one concurrent user.

I set the trace flag 1204 but am not seeing anything in the Error.log and I initiated a trace in profiler which does not seem to show any deadlock.
Despite having recreated the problem which show my browser hanging indefinitely. When I run the following queries:

SELECT spid, waittime, lastwaittype, waitresource
FROM master..sysprocesses
WHERE waittime > 10000
AND spid > 50

SELECT spid, cmd, status, loginame, open_tran, datediff(s, last_batch, getdate ()) AS [WaitTime(s)]
FROM master..sysprocesses p
WHERE open_tran > 0
AND spid > 50
AND datediff (s, last_batch, getdate ()) > 30
ANd EXISTS (SELECT * FROM master..syslockinfo l
WHERE req_spid = p.spid AND rsc_type <> 2)

I get:

55 860978 LCK_M_X PAG: 13:1:2573

54 AWAITING COMMAND sleeping sa 1 1499
55 UPDATE sleeping sa 2 1499

respectively. Any help would be welcome.

Thanks in advance,
DonThe queries will most likely not show you a deadlock situation. When SQL server detects a deadlock situation, one of the connections is immediately killed. Try this command, then run the deadlocking process again:

dbcc traceon (-1, 1204, 1205)|||I agree that the queries will only show hanging processes but that's what I found, if I am interpreting the results correctly. I did run the TRACEON command although only for 1204 but I cannot find any output from it in the Error log, in fact the last line in the log is:

2004-02-16 12:22:34.81 spid52 DBCC TRACEON 1204, server process ID (SPID) 52

Am I looking in the wrong spot?|||If I remember correctly, the -1 flag indicates that this flag is to be applied to all spids. If you do not specify the -1, and a deadlock occurs on a separate spid, then the deadlock is not recorded.|||I took your advice, I also discovered that flag 3605 will send that info to the error log. Every five seconds I get an entry to advise me there is no deadlock, but I am still getting the same freezing behaviour in my application.

I would make the assumption that the problem lies in the application however those queries I mentioned before are returning results to indicate my application SPID is waiting on a resource and the same code base operates on Oracle.

Any ideas?|||On the microsoft site there is a sp_blocker procedure which may help you - there is one there for sql 7 and 2000. I don't know how supported it is though!

The procedure will show you the SQL statments causing the blocking lock and the I also think is shows you the blocked SQL statement as well.

You would be wise to test it thoughly though before letting it loose on your prod sys though.|||First, thanks to MCrowley and dbabren. I appreciate your prompt and informative replies. It turns out that my problem was due to a select made just a few statements before the update that was holding a lock on that page.

Please forgive my ignorance in SQLServer but I have run accross many applications running on other DB's that perform a select as a page is entered to display the data to be modified and an update when the changes are submitted. It seems very strange that SQLServer would require me to lace the code with NOLOCK in order to prevent the initial selects from interfering with the subsequent updates. Admittedly the selects are more complicated than necessary due to an antiquated schema but I fail to see how SQLServer could remain competitive in the market with these types of oversights.

I must be missing something!|||I think you need to commit your select statements - that will release the shared locks for you. It sounds like the select and update statements are part of the same transaction - which makes sense if you are selecting for update purposes.

or

set the isolation level for each session to READ UNCOMMITTED which will allow dirty reads - it will also mean that your selects will take no shared lock. This is dangerous though, as data you are updating may be updated by another session at the same time - and one of the updates will be lost.|||This is a common problem in making the transfer from Oracle to SQL Server. In SQL Server, readers block writers. In Oracle, Readers just get outdated information. I am sure you could get a raging flame-war started on which is a better solution to the problem of concurrency.

Basic rule of thumb for all platforms, though, is get in and get out as quick as possible. With both reads, and writes.|||Originally posted by dbabren
set the isolation level for each session to READ UNCOMMITTED

No

it will also mean that your selects will take no shared lock. This is dangerous though

Yes

Basic rule of thumb for all platforms, though, is get in and get out as quick as possible.

Yes Yes

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.

DDL Permissions

Ok, folks, I got one for you. I want to allow a user to make schema changes to
tables in a database that are owned by dbo. However, I do not want this user to
do anything beyond that, such as add roles or change permissions on objects,
etc. It appears that the db_ddladmin fixed database role only allows the user to
create, delete, and alter objects owned by themselves. And, the only way to get
what I want is to add the user to the db_owner fixed database role. Not really
what I had in mind. Am I missing something here? Can anybody give me any
direction on this?
Thanks in advance. You guys rock!
Darrell
db_ddladmin can alter and drop object owned by others.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Darrell" <Darrell.Wright.nospam@.okc.gov> wrote in message
news:eLeP7MhNFHA.568@.TK2MSFTNGP09.phx.gbl...
> Ok, folks, I got one for you. I want to allow a user to make schema changes to tables in a
> database that are owned by dbo. However, I do not want this user to do anything beyond that, such
> as add roles or change permissions on objects, etc. It appears that the db_ddladmin fixed database
> role only allows the user to create, delete, and alter objects owned by themselves. And, the only
> way to get what I want is to add the user to the db_owner fixed database role. Not really what I
> had in mind. Am I missing something here? Can anybody give me any direction on this?
> Thanks in advance. You guys rock!
> Darrell
|||db_ddladmin is able to modify all tables in the database. Use Query Analyzer
to alter tables instead of Enterprise Manager if you don't want to see those
warning messages.
"Darrell" wrote:

> Ok, folks, I got one for you. I want to allow a user to make schema changes to
> tables in a database that are owned by dbo. However, I do not want this user to
> do anything beyond that, such as add roles or change permissions on objects,
> etc. It appears that the db_ddladmin fixed database role only allows the user to
> create, delete, and alter objects owned by themselves. And, the only way to get
> what I want is to add the user to the db_owner fixed database role. Not really
> what I had in mind. Am I missing something here? Can anybody give me any
> direction on this?
> Thanks in advance. You guys rock!
> Darrell
>
|||Jack wrote:
> db_ddladmin is able to modify all tables in the database. Use Query Analyzer
> to alter tables instead of Enterprise Manager if you don't want to see those
> warning messages.
> "Darrell" wrote:
>
Thanks to both of you for the responses. I like the Query Analyzer suggestion,
however, I have a bunch of GUI-loving developers that probably couldn't spell
T-SQL. But I digress...
A follow-up question, then, is can they make changes to the tables in the
database diagrammer and those changes will be saved back to the tables?
Thanks again.

DDL Permissions

Ok, folks, I got one for you. I want to allow a user to make schema changes
to
tables in a database that are owned by dbo. However, I do not want this user
to
do anything beyond that, such as add roles or change permissions on objects,
etc. It appears that the db_ddladmin fixed database role only allows the use
r to
create, delete, and alter objects owned by themselves. And, the only way to
get
what I want is to add the user to the db_owner fixed database role. Not real
ly
what I had in mind. Am I missing something here? Can anybody give me any
direction on this?
Thanks in advance. You guys rock!
Darrelldb_ddladmin can alter and drop object owned by others.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Darrell" <Darrell.Wright.nospam@.okc.gov> wrote in message
news:eLeP7MhNFHA.568@.TK2MSFTNGP09.phx.gbl...
> Ok, folks, I got one for you. I want to allow a user to make schema change
s to tables in a
> database that are owned by dbo. However, I do not want this user to do any
thing beyond that, such
> as add roles or change permissions on objects, etc. It appears that the db
_ddladmin fixed database
> role only allows the user to create, delete, and alter objects owned by th
emselves. And, the only
> way to get what I want is to add the user to the db_owner fixed database r
ole. Not really what I
> had in mind. Am I missing something here? Can anybody give me any directio
n on this?
> Thanks in advance. You guys rock!
> Darrell|||db_ddladmin is able to modify all tables in the database. Use Query Analyze
r
to alter tables instead of Enterprise Manager if you don't want to see those
warning messages.
"Darrell" wrote:

> Ok, folks, I got one for you. I want to allow a user to make schema change
s to
> tables in a database that are owned by dbo. However, I do not want this us
er to
> do anything beyond that, such as add roles or change permissions on object
s,
> etc. It appears that the db_ddladmin fixed database role only allows the u
ser to
> create, delete, and alter objects owned by themselves. And, the only way t
o get
> what I want is to add the user to the db_owner fixed database role. Not re
ally
> what I had in mind. Am I missing something here? Can anybody give me any
> direction on this?
> Thanks in advance. You guys rock!
> Darrell
>|||Jack wrote:
> db_ddladmin is able to modify all tables in the database. Use Query Analy
zer
> to alter tables instead of Enterprise Manager if you don't want to see tho
se
> warning messages.
> "Darrell" wrote:
>
Thanks to both of you for the responses. I like the Query Analyzer suggestio
n,
however, I have a bunch of GUI-loving developers that probably couldn't spel
l
T-SQL. But I digress...
A follow-up question, then, is can they make changes to the tables in the
database diagrammer and those changes will be saved back to the tables?
Thanks again.

DDL Permissions

Ok, folks, I got one for you. I want to allow a user to make schema changes to
tables in a database that are owned by dbo. However, I do not want this user to
do anything beyond that, such as add roles or change permissions on objects,
etc. It appears that the db_ddladmin fixed database role only allows the user to
create, delete, and alter objects owned by themselves. And, the only way to get
what I want is to add the user to the db_owner fixed database role. Not really
what I had in mind. Am I missing something here? Can anybody give me any
direction on this?
Thanks in advance. You guys rock!
Darrelldb_ddladmin can alter and drop object owned by others.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Darrell" <Darrell.Wright.nospam@.okc.gov> wrote in message
news:eLeP7MhNFHA.568@.TK2MSFTNGP09.phx.gbl...
> Ok, folks, I got one for you. I want to allow a user to make schema changes to tables in a
> database that are owned by dbo. However, I do not want this user to do anything beyond that, such
> as add roles or change permissions on objects, etc. It appears that the db_ddladmin fixed database
> role only allows the user to create, delete, and alter objects owned by themselves. And, the only
> way to get what I want is to add the user to the db_owner fixed database role. Not really what I
> had in mind. Am I missing something here? Can anybody give me any direction on this?
> Thanks in advance. You guys rock!
> Darrell|||db_ddladmin is able to modify all tables in the database. Use Query Analyzer
to alter tables instead of Enterprise Manager if you don't want to see those
warning messages.
"Darrell" wrote:
> Ok, folks, I got one for you. I want to allow a user to make schema changes to
> tables in a database that are owned by dbo. However, I do not want this user to
> do anything beyond that, such as add roles or change permissions on objects,
> etc. It appears that the db_ddladmin fixed database role only allows the user to
> create, delete, and alter objects owned by themselves. And, the only way to get
> what I want is to add the user to the db_owner fixed database role. Not really
> what I had in mind. Am I missing something here? Can anybody give me any
> direction on this?
> Thanks in advance. You guys rock!
> Darrell
>|||Jack wrote:
> db_ddladmin is able to modify all tables in the database. Use Query Analyzer
> to alter tables instead of Enterprise Manager if you don't want to see those
> warning messages.
> "Darrell" wrote:
>
Thanks to both of you for the responses. I like the Query Analyzer suggestion,
however, I have a bunch of GUI-loving developers that probably couldn't spell
T-SQL. But I digress...
A follow-up question, then, is can they make changes to the tables in the
database diagrammer and those changes will be saved back to the tables?
Thanks again.

DBTYPE_DBTIMESTAMP OLE DB question

Folks,
I am trying to use ole db to read a date field created as a DATETIME in C#.
It only seems to work if I set the binding type (wType) to
DBTYPE_DBTIMESTAMP. When I do this, 16 bytes of data are written to my
buffer. The question then is what to do with this raw data. What should I
cast it to, so that I can make use of it? I can't think of date/time types
that are 16 bytes long.
Thank you,
Matthew FlemingHi
The SQL Server data type "Timestamp" has nothing to do with date or time. It
is a binary number that is sequential and is generally used for concurrency
control in applications.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"dermite" <dermite@.discussions.microsoft.com> wrote in message
news:614ADA8C-E424-4419-9FBC-B86C93646E80@.microsoft.com...
> Folks,
> I am trying to use ole db to read a date field created as a DATETIME in
> C#.
> It only seems to work if I set the binding type (wType) to
> DBTYPE_DBTIMESTAMP. When I do this, 16 bytes of data are written to my
> buffer. The question then is what to do with this raw data. What should I
> cast it to, so that I can make use of it? I can't think of date/time types
> that are 16 bytes long.
> Thank you,
> Matthew Fleming|||If this is so, then why is the field (which was created as type DATETIME),
readable only with a binding type of DBTYPE_DBTIMESTAMP? I tried
DBTYPE_DATE and it did not work (0 bytes were written to the buffer).
Matthew Fleming
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> The SQL Server data type "Timestamp" has nothing to do with date or time.
It
> is a binary number that is sequential and is generally used for concurrenc
y
> control in applications.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "dermite" <dermite@.discussions.microsoft.com> wrote in message
> news:614ADA8C-E424-4419-9FBC-B86C93646E80@.microsoft.com...
>
>|||Mike Epprecht (SQL MVP) (mike@.epprecht.net) writes:
> The SQL Server data type "Timestamp" has nothing to do with date or
> time. It is a binary number that is sequential and is generally used for
> concurrency control in applications.
Yes, but the OLE DB data type DBTYPE_DBTIMESTAMP has everything to do
with date and time. That is in fact how you get back the datetime data type
from SQL Server.
Anyway, the actual question have been sorted out in
microsoft.public.olddb.data. Please to do not post the same question to
different newsgroups independently!
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