DEADLOCKS

Hi:
I have a .net web application and I am getting a lot of deadlocks. My
system processes a lot of real-time transactions daily. In addition ,
there are like 3 DTS packages running. The problem is that I am getting a
lot of deadlock and based on the history of the pakages I would say that is
happening because the packages take the control and kill the processes that
are tying to run at the same time.
Is there a way to try to reduce this dead-lock assigning especific features
to the packages.
ThanksI don't think there are any issues specific to DTS packages that cause
deadlocking. A package could be excuting almost anything, so provide more
details about what type of selects / updates / inserts the package is
performing and also the nature of the transactions that are being blocked.
Read up in Books Online about "set transaction isolation level". Consider
using a lower isolation level, like read uncommitted. Basically, this allows
a process to read data currently locked by another transaction. Just
understand what "dirty reads", "nonrepeatable reads", etc. are and whether
or not they would present a significant problem in the specific case of your
queries.
Also, for what it's worth, here are my bookmarks for SQL Server deadlocking:
Deadlocking
http://msdn.microsoft.com/library/d... />
a_8i93.asp
INF: Analyzing and Avoiding Deadlocks in SQL Server
http://support.microsoft.com/defaul...kb;en-us;169960
Tracing Deadlocks
http://www.sqlservercentral.com/col...ngdeadlocks.asp
Minimizing Deadlocks
http://msdn.microsoft.com/library/d... />
a_3hdf.asp
SQL Server technical bulletin - How to resolve a deadlock
http://support.microsoft.com/defaul...kb;en-us;832524
"Gina Hernandez" <pdwhitt@.nospam.wdsinc.com> wrote in message
news:%23OfWoX19FHA.4004@.TK2MSFTNGP14.phx.gbl...
> Hi:
> I have a .net web application and I am getting a lot of deadlocks. My
> system processes a lot of real-time transactions daily. In addition ,
> there are like 3 DTS packages running. The problem is that I am getting
> a lot of deadlock and based on the history of the pakages I would say that
> is happening because the packages take the control and kill the processes
> that are tying to run at the same time.
> Is there a way to try to reduce this dead-lock assigning especific
> features to the packages.
> Thanks
>

Deadlocking Question

I have a ColdFusion web application running on a SQL Server 7.0 back-end.

Within this application, there is are three queries in a row where the second query deadlocks 1-2 times a day, which is too high. The queries do the following:

1. Insert member details from a web form into member table
2. Select ID (key) of what was just inserted into the member table
3. Update a third table with the member ID

As I said earlier, the 2nd query is the one that I see deadlocked in the ColdFusion error logs. I am unable to replicate the problem, so I have not been able to troubleshoot using the procedures described in SQL-BOL (unless I am mis-understanding the documentation).

This sequence runs an average of 150 times per day, but it can be anywhere from 100 to 500 times, so the failure rate is about 1%.

Any ideas on why this is happening and what I can do to prevent it?

Thanks,
CybermudA spid that does only one thing can't deadlock, it isn't possible.

When a spid accesses an object, it normally takes a lock (of some kind) on that object.

When a spid has an exclusive lock on an object, and another spid tries to access that object, the new spid is "blocked". When a spid is blocked, it stops executing until the object that is causing the blocking becomes available again.

When two spids are running (lets call them 69 and 70), spid 69 locks object A, spid 70 locks object B and everything is still happy. Then 69 attempts to lock object B, but it becomes blocked because 70 already has it locked. Then 70 tries to lock object A, which causes it to be blocked and now we have a deadlock! Both spids are blocked, waiting for each other. SQL Server detects this condition, and picks one of the deadlocked spids as the "victim" and automagically kills the victim (allowing the other spid to proceed).

The best way to avoid deadlocks is to keep your locks small. Don't lock objects for long periods of time. When you do have to lock objects, try to always lock them in the same sequence so that blocks rarely become deadlocks.

If you set Trace flag 1204 (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ta-tz_646r.asp), you'll get more diagnostic information about the cause of the deadlock in your SQL errorlog file.

-PatP|||Pat:

Thanks for the reply. I do not understand what you mean when you say

"A spid that does only one thing can't deadlock, it isn't possible."

Are you saying that since there is only one Cold Fusion application, there will only be one spid? I do understand the principles of deadlocking but can't seem to figure out how to apply them to my situation.

Also, is there any performance hit from leaving trace flag 1204 on for an extended period of time?

Thanks,
Cybermud|||If you think about what a deadlock is, an spid with only one object locked CAN'T deadlock. In order for a deadlock to occur, you must already have one object locked, then try to lock another.

Spid usage depends on how your ColdFusion engine is configured, but typically it will support many threads (therefore many spids). R937 would be able to answer this kind of question much better than I can, although he might prefer you to post it in the ColdFusion (http://www.dbforums.com/f223) forum.

While traceflag 1204 used to impose some significant overhead, I don't believe that is the case anymore. I'd go ahead and run it for a while, but watch for any signs of server distress (just in case!).

-PatP|||Pat:

Thanks for the quick reply. I think I am understanding it correctly...that sequence of the three queries can't be causing a deadlock by itself, because its only one spid, right?

That means there is something else going on...and I will be taking this to the ColdFusion forum.

I also plan on leaving flag 1204 on for the night to see if it turns anything up. Thanks again for all your help.

Cybermud

deadlock on a single Select not in a transaction

Hi,
We have a web application developped in asp.net (I think it's not relevant,
but well, it's so you know)... Yesterday, we received the following message
"Transaction (Process ID 69) was deadlocked on lock resources with another
process and has been chosen as the deadlock victim. Rerun the transaction. "
The thing is, the query was a simple select with inner joins between 3
tables (like select fields from table1 inner join table2... inner join
table3...). This command is not in a transaction, so the deadlock seems
impossible. And moreover, the deadlock occurs on the DataReader.Read() not
on the Command.ExecuteReader(...).
Can someone explain why the deadlock can have occured and what could be the
cause and solution to it? Can it be a bug in SQL Server or in the .net
framework (I really doubt about it)?
thanks
ThunderMusic
What you're seeing is not concerning a Transaction as you are imagining it.
In essence, every operation performed by SQL Server is a transaction.
However, a Transaction (capital T) is a grouping of transactions (or
operations) into a single atomic unit which either fails or succeeds as a
whole. In this case, the reference is to to a (small t) transaction, which
is the operation of your query.
The exception occurs when 2 transactions (or processes) are trying to access
the same database object (such as a row in a table) at the same time. Each
process tries to get a lock on the object, and only one can. The other is
therefore killed. Here's a good article about this, and how to deal with it.
Notice that the most common tactic is simply to try again:
http://www.sql-server-performance.com/deadlocks.asp
HTH,
Kevin Spencer
Microsoft MVP
Printing Components, Email Components,
FTP Client Classes, Enhanced Data Controls, much more.
DSI PrintManager, Miradyne Component Libraries:
http://www.miradyne.net
"ThunderMusic" <NoSpAmdanlatathotmaildotcom@.NoSpAm.com> wrote in message
news:ewkig6PiHHA.2028@.TK2MSFTNGP03.phx.gbl...
> Hi,
> We have a web application developped in asp.net (I think it's not
> relevant, but well, it's so you know)... Yesterday, we received the
> following message "Transaction (Process ID 69) was deadlocked on lock
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction. " The thing is, the query was a simple select with
> inner joins between 3 tables (like select fields from table1 inner join
> table2... inner join table3...). This command is not in a transaction, so
> the deadlock seems impossible. And moreover, the deadlock occurs on the
> DataReader.Read() not on the Command.ExecuteReader(...).
> Can someone explain why the deadlock can have occured and what could be
> the cause and solution to it? Can it be a bug in SQL Server or in the .net
> framework (I really doubt about it)?
> thanks
> ThunderMusic
>
|||You might enjoy reading the three articles starting at:
http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx
The bottom line is that a select is a single statement transaction that can
hold locks, and a conflicting desire for locks is what causes deadlocks.
(The Deadly Embrace, where each process is holding something the other
process wants.)
RLF
"ThunderMusic" <NoSpAmdanlatathotmaildotcom@.NoSpAm.com> wrote in message
news:ewkig6PiHHA.2028@.TK2MSFTNGP03.phx.gbl...
> Hi,
> We have a web application developped in asp.net (I think it's not
> relevant, but well, it's so you know)... Yesterday, we received the
> following message "Transaction (Process ID 69) was deadlocked on lock
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction. " The thing is, the query was a simple select with
> inner joins between 3 tables (like select fields from table1 inner join
> table2... inner join table3...). This command is not in a transaction, so
> the deadlock seems impossible. And moreover, the deadlock occurs on the
> DataReader.Read() not on the Command.ExecuteReader(...).
> Can someone explain why the deadlock can have occured and what could be
> the cause and solution to it? Can it be a bug in SQL Server or in the .net
> framework (I really doubt about it)?
> thanks
> ThunderMusic
>
|||Kevin Spencer wrote:
...
> The exception occurs when 2 transactions (or processes) are trying to access
> the same database object (such as a row in a table) at the same time. Each
> process tries to get a lock on the object, and only one can. The other is
> therefore killed. Here's a good article about this, and how to deal with it.
> Notice that the most common tactic is simply to try again:
...
It's bit simplified, what you describe is not a deadlock situation. One
transaction will happily wait for lock to be released. Typical deadlock
situation is:
Transaction1 holds lock on A
Transcation2 holds lock on B
Transaction1 wants lock on B
Transcation2 wants lock on A
This cannot be resolved by waiting, so one transaction has to be killed.
Just a clarification.
Regards,
Goran

Deadlock issue (JAVA + SQL server)

I have a java web based application, which was build and designed keeping
oracle in mind. The application also used a 3rd party API "cocobase" which
used to do most of the data interraction. But there were many issue after the
application is ported to work with SQL database and the most prominant issue
is a Deadlock issue. We need to resolve the deadlock issue first. I found out
the deadlock is happening one particular table say 'tableX'
I have tried the following.
1.
Findings : There are Selects, Inserts, Delete and Updates hapenning in the
same table (tableX) for numerous times. These DML statements were executed
using cocobase calls. The Insert, Delete and Updates were targeted first as
deadlock were happening mostly after an Insert or Update. Insert/Update was
taking some time to execute.
Action taken : The Insert/Delete/Updates were changed from cocobase calls to
direct jdbc calls.
Benefit : The time taken by cocobase to prepare the statement was saved.
2.
Findings : After the change, the Insert/Updates were being executed as
normal sql statements using direct TSQL e.g insert into table1 values ('abc',
123). Each time the query is executed the sql engine would compile the
statement.
Action taken : The Insert/Updates were changed to execute using prepared
statements. e.g exec sp_executesql 'insert into table1 values (?, ?)', '@.p1
varchar, @.p2 int', 'abc', 123
Benefit : The statements used to execute faster. If the query is fired once,
then the sql engine will not compile the statement the next time it is fired
(even by other transactions) with different parameter values.
3.
Findings : After the changes, the deadlock still persist, but this time the
deadlock were occuring mostly on the sp_cursoropen or sp_cursorfetch
execution. It was found the the select statement were opening cursors and the
cursors were getting closed in the end while closing the connection object.
The cursor or statements need to be closed right after use.
Action taken : The "Select" statements were changed from cocobase to direct
jdbc calls. Most of the cursors or statements pertaining to the product table
were closed after use. There were 2 or 3 places where the changes couldn't be
applied cause of the complexity in the code. The recordset object itself were
being dynamically created and used in a baseclass.
Benefit : The locking period of a resource is reduced, if the
cursor/statement is closed after use.
4.
Findings : Every select query was opening a cursor which used to lock the
resource in the database. Using cursor was not necessary as in the
application when a query is fired or when a data is fetched, it is ideally
pertaining to one batch, which is not very huge. If we could avoid using
cursor, the locking would reduce and would help us resolve the deadlock issue.
Action taken : The "SelectMethod" in the connectionstring or URL was changed
from "Cursor" to "Direct". But due to the changes, the application chrashed
and the change didnt work. Hence it was reverted back.
Benefit : None, as the changes didnt work for the application.
5.
Findings : The other cocobase calls to their tables like "CB_TABLES",
"CB_OBJECTS", "CB_FIELDS", "CB_CLAUSES" were taking much time to execute.
There was a index on the OBJECTNAME field, but was a nonclustered index.
Action taken : The existing nonclustered index was changed to a clustered
index.
Benefit : The query executed faster as initially the sql engine was doing a
"table scan" and now it was doing a "cluster index seek".
Is there anything else anyone suggest to handle and resolve the deadlock?
Any advise would be appreciated.
--
Cheers
--Hi
"Vikram" wrote:
> I have a java web based application, which was build and designed keeping
> oracle in mind. The application also used a 3rd party API "cocobase" which
> used to do most of the data interraction. But there were many issue after the
> application is ported to work with SQL database and the most prominant issue
> is a Deadlock issue. We need to resolve the deadlock issue first. I found out
> the deadlock is happening one particular table say 'tableX'
> I have tried the following.
> 1.
> Findings : There are Selects, Inserts, Delete and Updates hapenning in the
> same table (tableX) for numerous times. These DML statements were executed
> using cocobase calls. The Insert, Delete and Updates were targeted first as
> deadlock were happening mostly after an Insert or Update. Insert/Update was
> taking some time to execute.
> Action taken : The Insert/Delete/Updates were changed from cocobase calls to
> direct jdbc calls.
> Benefit : The time taken by cocobase to prepare the statement was saved.
> 2.
> Findings : After the change, the Insert/Updates were being executed as
> normal sql statements using direct TSQL e.g insert into table1 values ('abc',
> 123). Each time the query is executed the sql engine would compile the
> statement.
> Action taken : The Insert/Updates were changed to execute using prepared
> statements. e.g exec sp_executesql 'insert into table1 values (?, ?)', '@.p1
> varchar, @.p2 int', 'abc', 123
> Benefit : The statements used to execute faster. If the query is fired once,
> then the sql engine will not compile the statement the next time it is fired
> (even by other transactions) with different parameter values.
> 3.
> Findings : After the changes, the deadlock still persist, but this time the
> deadlock were occuring mostly on the sp_cursoropen or sp_cursorfetch
> execution. It was found the the select statement were opening cursors and the
> cursors were getting closed in the end while closing the connection object.
> The cursor or statements need to be closed right after use.
> Action taken : The "Select" statements were changed from cocobase to direct
> jdbc calls. Most of the cursors or statements pertaining to the product table
> were closed after use. There were 2 or 3 places where the changes couldn't be
> applied cause of the complexity in the code. The recordset object itself were
> being dynamically created and used in a baseclass.
> Benefit : The locking period of a resource is reduced, if the
> cursor/statement is closed after use.
> 4.
> Findings : Every select query was opening a cursor which used to lock the
> resource in the database. Using cursor was not necessary as in the
> application when a query is fired or when a data is fetched, it is ideally
> pertaining to one batch, which is not very huge. If we could avoid using
> cursor, the locking would reduce and would help us resolve the deadlock issue.
> Action taken : The "SelectMethod" in the connectionstring or URL was changed
> from "Cursor" to "Direct". But due to the changes, the application chrashed
> and the change didnt work. Hence it was reverted back.
> Benefit : None, as the changes didnt work for the application.
> 5.
> Findings : The other cocobase calls to their tables like "CB_TABLES",
> "CB_OBJECTS", "CB_FIELDS", "CB_CLAUSES" were taking much time to execute.
> There was a index on the OBJECTNAME field, but was a nonclustered index.
> Action taken : The existing nonclustered index was changed to a clustered
> index.
> Benefit : The query executed faster as initially the sql engine was doing a
> "table scan" and now it was doing a "cluster index seek".
> Is there anything else anyone suggest to handle and resolve the deadlock?
> Any advise would be appreciated.
> --
> Cheers
> --
You don't give the version of SQL Server you are using? There are
enhancements in SQL Server 2005 that make these easier to track down and
diagnose.
If you process is actually being blocked and not deadlocked then check out:
http://support.microsoft.com/default.aspx/kb/271509/EN-US/
If you have deadlocks it usually require two or more processes that trying
to access the same resources but in different orders. See
http://support.microsoft.com/kb/832524/en-us
John|||On May 16, 9:58 am, Vikram <Vik...@.discussions.microsoft.com> wrote:
> I have a java web based application, which was build and designed keeping
> oracle in mind. The application also used a 3rd party API "cocobase" which
> used to do most of the data interraction. But there were many issue after the
> application is ported to work with SQL database and the most prominant issue
> is a Deadlock issue. We need to resolve the deadlock issue first. I found out
> the deadlock is happening one particular table say 'tableX'
> I have tried the following.
> 1.
> Findings : There are Selects, Inserts, Delete and Updates hapenning in the
> same table (tableX) for numerous times. These DML statements were executed
> using cocobase calls. The Insert, Delete and Updates were targeted first as
> deadlock were happening mostly after an Insert or Update. Insert/Update was
> taking some time to execute.
> Action taken : The Insert/Delete/Updates were changed from cocobase calls to
> direct jdbc calls.
> Benefit : The time taken by cocobase to prepare the statement was saved.
> 2.
> Findings : After the change, the Insert/Updates were being executed as
> normal sql statements using direct TSQL e.g insert into table1 values ('abc',
> 123). Each time the query is executed the sql engine would compile the
> statement.
> Action taken : The Insert/Updates were changed to execute using prepared
> statements. e.g exec sp_executesql 'insert into table1 values (?, ?)', '@.p1
> varchar, @.p2 int', 'abc', 123
> Benefit : The statements used to execute faster. If the query is fired once,
> then the sql engine will not compile the statement the next time it is fired
> (even by other transactions) with different parameter values.
> 3.
> Findings : After the changes, the deadlock still persist, but this time the
> deadlock were occuring mostly on the sp_cursoropen or sp_cursorfetch
> execution. It was found the the select statement were opening cursors and the
> cursors were getting closed in the end while closing the connection object.
> The cursor or statements need to be closed right after use.
> Action taken : The "Select" statements were changed from cocobase to direct
> jdbc calls. Most of the cursors or statements pertaining to the product table
> were closed after use. There were 2 or 3 places where the changes couldn't be
> applied cause of the complexity in the code. The recordset object itself were
> being dynamically created and used in a baseclass.
> Benefit : The locking period of a resource is reduced, if the
> cursor/statement is closed after use.
> 4.
> Findings : Every select query was opening a cursor which used to lock the
> resource in the database. Using cursor was not necessary as in the
> application when a query is fired or when a data is fetched, it is ideally
> pertaining to one batch, which is not very huge. If we could avoid using
> cursor, the locking would reduce and would help us resolve the deadlock issue.
> Action taken : The "SelectMethod" in the connectionstring or URL was changed
> from "Cursor" to "Direct". But due to the changes, the application chrashed
> and the change didnt work. Hence it was reverted back.
> Benefit : None, as the changes didnt work for the application.
> 5.
> Findings : The other cocobase calls to their tables like "CB_TABLES",
> "CB_OBJECTS", "CB_FIELDS", "CB_CLAUSES" were taking much time to execute.
> There was a index on the OBJECTNAME field, but was a nonclustered index.
> Action taken : The existing nonclustered index was changed to a clustered
> index.
> Benefit : The query executed faster as initially the sql engine was doing a
> "table scan" and now it was doing a "cluster index seek".
> Is there anything else anyone suggest to handle and resolve the deadlock?
> Any advise would be appreciated.
> --
> Cheers
> --
1. Are you using trasaction . If yes , try to make duration of
transaction execution short by removing unwanted statements from the
transaction
2. Eventhough you have created indexes sql server may go for table
scan . Check the execution plan and provide appropriate index hints
on tables like
from table with (index(indexname))
3. Use the tables in the same order in different transactions
4. What is the isolation level used . If it is SERIALIZABLE, check
whether READ COMMITED is OK for your logic
5. If you use components , default isolation level may be SERIALIZABLE
try to change it to READ COMMITED|||> Benefit : The query executed faster as initially the sql engine was doing
> a
> "table scan" and now it was doing a "cluster index seek".
> Is there anything else anyone suggest to handle and resolve the deadlock?
> Any advise would be appreciated.
In addition to John's suggestions, take a look at execution plans of other
queries to ensure data are accessed using seeks rather than scans. Scans
are notorious for contributing to deadlocking and blocking. Also, you might
need to add locking hints in certain situations.
Hope this helps.
Dan Guzman
SQL Server MVP
"Vikram" <Vikram@.discussions.microsoft.com> wrote in message
news:BC820B12-579C-49A8-918F-3CD10973C270@.microsoft.com...
>I have a java web based application, which was build and designed keeping
> oracle in mind. The application also used a 3rd party API "cocobase" which
> used to do most of the data interraction. But there were many issue after
> the
> application is ported to work with SQL database and the most prominant
> issue
> is a Deadlock issue. We need to resolve the deadlock issue first. I found
> out
> the deadlock is happening one particular table say 'tableX'
> I have tried the following.
> 1.
> Findings : There are Selects, Inserts, Delete and Updates hapenning in the
> same table (tableX) for numerous times. These DML statements were executed
> using cocobase calls. The Insert, Delete and Updates were targeted first
> as
> deadlock were happening mostly after an Insert or Update. Insert/Update
> was
> taking some time to execute.
> Action taken : The Insert/Delete/Updates were changed from cocobase calls
> to
> direct jdbc calls.
> Benefit : The time taken by cocobase to prepare the statement was saved.
> 2.
> Findings : After the change, the Insert/Updates were being executed as
> normal sql statements using direct TSQL e.g insert into table1 values
> ('abc',
> 123). Each time the query is executed the sql engine would compile the
> statement.
> Action taken : The Insert/Updates were changed to execute using prepared
> statements. e.g exec sp_executesql 'insert into table1 values (?, ?)',
> '@.p1
> varchar, @.p2 int', 'abc', 123
> Benefit : The statements used to execute faster. If the query is fired
> once,
> then the sql engine will not compile the statement the next time it is
> fired
> (even by other transactions) with different parameter values.
> 3.
> Findings : After the changes, the deadlock still persist, but this time
> the
> deadlock were occuring mostly on the sp_cursoropen or sp_cursorfetch
> execution. It was found the the select statement were opening cursors and
> the
> cursors were getting closed in the end while closing the connection
> object.
> The cursor or statements need to be closed right after use.
> Action taken : The "Select" statements were changed from cocobase to
> direct
> jdbc calls. Most of the cursors or statements pertaining to the product
> table
> were closed after use. There were 2 or 3 places where the changes couldn't
> be
> applied cause of the complexity in the code. The recordset object itself
> were
> being dynamically created and used in a baseclass.
> Benefit : The locking period of a resource is reduced, if the
> cursor/statement is closed after use.
> 4.
> Findings : Every select query was opening a cursor which used to lock the
> resource in the database. Using cursor was not necessary as in the
> application when a query is fired or when a data is fetched, it is ideally
> pertaining to one batch, which is not very huge. If we could avoid using
> cursor, the locking would reduce and would help us resolve the deadlock
> issue.
> Action taken : The "SelectMethod" in the connectionstring or URL was
> changed
> from "Cursor" to "Direct". But due to the changes, the application
> chrashed
> and the change didnt work. Hence it was reverted back.
> Benefit : None, as the changes didnt work for the application.
> 5.
> Findings : The other cocobase calls to their tables like "CB_TABLES",
> "CB_OBJECTS", "CB_FIELDS", "CB_CLAUSES" were taking much time to execute.
> There was a index on the OBJECTNAME field, but was a nonclustered index.
> Action taken : The existing nonclustered index was changed to a clustered
> index.
> Benefit : The query executed faster as initially the sql engine was doing
> a
> "table scan" and now it was doing a "cluster index seek".
> Is there anything else anyone suggest to handle and resolve the deadlock?
> Any advise would be appreciated.
> --
> Cheers
> --
>

Deadlock issue (JAVA + SQL server)

I have a java web based application, which was build and designed keeping
oracle in mind. The application also used a 3rd party API "cocobase" which
used to do most of the data interraction. But there were many issue after the
application is ported to work with SQL database and the most prominant issue
is a Deadlock issue. We need to resolve the deadlock issue first. I found out
the deadlock is happening one particular table say 'tableX'
I have tried the following.
1.
Findings : There are Selects, Inserts, Delete and Updates hapenning in the
same table (tableX) for numerous times. These DML statements were executed
using cocobase calls. The Insert, Delete and Updates were targeted first as
deadlock were happening mostly after an Insert or Update. Insert/Update was
taking some time to execute.
Action taken : The Insert/Delete/Updates were changed from cocobase calls to
direct jdbc calls.
Benefit : The time taken by cocobase to prepare the statement was saved.
2.
Findings : After the change, the Insert/Updates were being executed as
normal sql statements using direct TSQL e.g insert into table1 values ('abc',
123). Each time the query is executed the sql engine would compile the
statement.
Action taken : The Insert/Updates were changed to execute using prepared
statements. e.g exec sp_executesql 'insert into table1 values (?, ?)', '@.p1
varchar, @.p2 int', 'abc', 123
Benefit : The statements used to execute faster. If the query is fired once,
then the sql engine will not compile the statement the next time it is fired
(even by other transactions) with different parameter values.
3.
Findings : After the changes, the deadlock still persist, but this time the
deadlock were occuring mostly on the sp_cursoropen or sp_cursorfetch
execution. It was found the the select statement were opening cursors and the
cursors were getting closed in the end while closing the connection object.
The cursor or statements need to be closed right after use.
Action taken : The "Select" statements were changed from cocobase to direct
jdbc calls. Most of the cursors or statements pertaining to the product table
were closed after use. There were 2 or 3 places where the changes couldn't be
applied cause of the complexity in the code. The recordset object itself were
being dynamically created and used in a baseclass.
Benefit : The locking period of a resource is reduced, if the
cursor/statement is closed after use.
4.
Findings : Every select query was opening a cursor which used to lock the
resource in the database. Using cursor was not necessary as in the
application when a query is fired or when a data is fetched, it is ideally
pertaining to one batch, which is not very huge. If we could avoid using
cursor, the locking would reduce and would help us resolve the deadlock issue.
Action taken : The "SelectMethod" in the connectionstring or URL was changed
from "Cursor" to "Direct". But due to the changes, the application chrashed
and the change didnt work. Hence it was reverted back.
Benefit : None, as the changes didnt work for the application.
5.
Findings : The other cocobase calls to their tables like "CB_TABLES",
"CB_OBJECTS", "CB_FIELDS", "CB_CLAUSES" were taking much time to execute.
There was a index on the OBJECTNAME field, but was a nonclustered index.
Action taken : The existing nonclustered index was changed to a clustered
index.
Benefit : The query executed faster as initially the sql engine was doing a
"table scan" and now it was doing a "cluster index seek".
Is there anything else anyone suggest to handle and resolve the deadlock?
Any advise would be appreciated.
Cheers
Hi
"Vikram" wrote:

> I have a java web based application, which was build and designed keeping
> oracle in mind. The application also used a 3rd party API "cocobase" which
> used to do most of the data interraction. But there were many issue after the
> application is ported to work with SQL database and the most prominant issue
> is a Deadlock issue. We need to resolve the deadlock issue first. I found out
> the deadlock is happening one particular table say 'tableX'
> I have tried the following.
> 1.
> Findings : There are Selects, Inserts, Delete and Updates hapenning in the
> same table (tableX) for numerous times. These DML statements were executed
> using cocobase calls. The Insert, Delete and Updates were targeted first as
> deadlock were happening mostly after an Insert or Update. Insert/Update was
> taking some time to execute.
> Action taken : The Insert/Delete/Updates were changed from cocobase calls to
> direct jdbc calls.
> Benefit : The time taken by cocobase to prepare the statement was saved.
> 2.
> Findings : After the change, the Insert/Updates were being executed as
> normal sql statements using direct TSQL e.g insert into table1 values ('abc',
> 123). Each time the query is executed the sql engine would compile the
> statement.
> Action taken : The Insert/Updates were changed to execute using prepared
> statements. e.g exec sp_executesql 'insert into table1 values (?, ?)', '@.p1
> varchar, @.p2 int', 'abc', 123
> Benefit : The statements used to execute faster. If the query is fired once,
> then the sql engine will not compile the statement the next time it is fired
> (even by other transactions) with different parameter values.
> 3.
> Findings : After the changes, the deadlock still persist, but this time the
> deadlock were occuring mostly on the sp_cursoropen or sp_cursorfetch
> execution. It was found the the select statement were opening cursors and the
> cursors were getting closed in the end while closing the connection object.
> The cursor or statements need to be closed right after use.
> Action taken : The "Select" statements were changed from cocobase to direct
> jdbc calls. Most of the cursors or statements pertaining to the product table
> were closed after use. There were 2 or 3 places where the changes couldn't be
> applied cause of the complexity in the code. The recordset object itself were
> being dynamically created and used in a baseclass.
> Benefit : The locking period of a resource is reduced, if the
> cursor/statement is closed after use.
> 4.
> Findings : Every select query was opening a cursor which used to lock the
> resource in the database. Using cursor was not necessary as in the
> application when a query is fired or when a data is fetched, it is ideally
> pertaining to one batch, which is not very huge. If we could avoid using
> cursor, the locking would reduce and would help us resolve the deadlock issue.
> Action taken : The "SelectMethod" in the connectionstring or URL was changed
> from "Cursor" to "Direct". But due to the changes, the application chrashed
> and the change didnt work. Hence it was reverted back.
> Benefit : None, as the changes didnt work for the application.
> 5.
> Findings : The other cocobase calls to their tables like "CB_TABLES",
> "CB_OBJECTS", "CB_FIELDS", "CB_CLAUSES" were taking much time to execute.
> There was a index on the OBJECTNAME field, but was a nonclustered index.
> Action taken : The existing nonclustered index was changed to a clustered
> index.
> Benefit : The query executed faster as initially the sql engine was doing a
> "table scan" and now it was doing a "cluster index seek".
> Is there anything else anyone suggest to handle and resolve the deadlock?
> Any advise would be appreciated.
> --
> Cheers
> --
You don't give the version of SQL Server you are using? There are
enhancements in SQL Server 2005 that make these easier to track down and
diagnose.
If you process is actually being blocked and not deadlocked then check out:
http://support.microsoft.com/default.aspx/kb/271509/EN-US/
If you have deadlocks it usually require two or more processes that trying
to access the same resources but in different orders. See
http://support.microsoft.com/kb/832524/en-us
John
|||On May 16, 9:58 am, Vikram <Vik...@.discussions.microsoft.com> wrote:
> I have a java web based application, which was build and designed keeping
> oracle in mind. The application also used a 3rd party API "cocobase" which
> used to do most of the data interraction. But there were many issue after the
> application is ported to work with SQL database and the most prominant issue
> is a Deadlock issue. We need to resolve the deadlock issue first. I found out
> the deadlock is happening one particular table say 'tableX'
> I have tried the following.
> 1.
> Findings : There are Selects, Inserts, Delete and Updates hapenning in the
> same table (tableX) for numerous times. These DML statements were executed
> using cocobase calls. The Insert, Delete and Updates were targeted first as
> deadlock were happening mostly after an Insert or Update. Insert/Update was
> taking some time to execute.
> Action taken : The Insert/Delete/Updates were changed from cocobase calls to
> direct jdbc calls.
> Benefit : The time taken by cocobase to prepare the statement was saved.
> 2.
> Findings : After the change, the Insert/Updates were being executed as
> normal sql statements using direct TSQL e.g insert into table1 values ('abc',
> 123). Each time the query is executed the sql engine would compile the
> statement.
> Action taken : The Insert/Updates were changed to execute using prepared
> statements. e.g exec sp_executesql 'insert into table1 values (?, ?)', '@.p1
> varchar, @.p2 int', 'abc', 123
> Benefit : The statements used to execute faster. If the query is fired once,
> then the sql engine will not compile the statement the next time it is fired
> (even by other transactions) with different parameter values.
> 3.
> Findings : After the changes, the deadlock still persist, but this time the
> deadlock were occuring mostly on the sp_cursoropen or sp_cursorfetch
> execution. It was found the the select statement were opening cursors and the
> cursors were getting closed in the end while closing the connection object.
> The cursor or statements need to be closed right after use.
> Action taken : The "Select" statements were changed from cocobase to direct
> jdbc calls. Most of the cursors or statements pertaining to the product table
> were closed after use. There were 2 or 3 places where the changes couldn't be
> applied cause of the complexity in the code. The recordset object itself were
> being dynamically created and used in a baseclass.
> Benefit : The locking period of a resource is reduced, if the
> cursor/statement is closed after use.
> 4.
> Findings : Every select query was opening a cursor which used to lock the
> resource in the database. Using cursor was not necessary as in the
> application when a query is fired or when a data is fetched, it is ideally
> pertaining to one batch, which is not very huge. If we could avoid using
> cursor, the locking would reduce and would help us resolve the deadlock issue.
> Action taken : The "SelectMethod" in the connectionstring or URL was changed
> from "Cursor" to "Direct". But due to the changes, the application chrashed
> and the change didnt work. Hence it was reverted back.
> Benefit : None, as the changes didnt work for the application.
> 5.
> Findings : The other cocobase calls to their tables like "CB_TABLES",
> "CB_OBJECTS", "CB_FIELDS", "CB_CLAUSES" were taking much time to execute.
> There was a index on the OBJECTNAME field, but was a nonclustered index.
> Action taken : The existing nonclustered index was changed to a clustered
> index.
> Benefit : The query executed faster as initially the sql engine was doing a
> "table scan" and now it was doing a "cluster index seek".
> Is there anything else anyone suggest to handle and resolve the deadlock?
> Any advise would be appreciated.
> --
> Cheers
> --
1. Are you using trasaction . If yes , try to make duration of
transaction execution short by removing unwanted statements from the
transaction
2. Eventhough you have created indexes sql server may go for table
scan . Check the execution plan and provide appropriate index hints
on tables like
from table with (index(indexname))
3. Use the tables in the same order in different transactions
4. What is the isolation level used . If it is SERIALIZABLE, check
whether READ COMMITED is OK for your logic
5. If you use components , default isolation level may be SERIALIZABLE
try to change it to READ COMMITED
|||> Benefit : The query executed faster as initially the sql engine was doing
> a
> "table scan" and now it was doing a "cluster index seek".
> Is there anything else anyone suggest to handle and resolve the deadlock?
> Any advise would be appreciated.
In addition to John's suggestions, take a look at execution plans of other
queries to ensure data are accessed using seeks rather than scans. Scans
are notorious for contributing to deadlocking and blocking. Also, you might
need to add locking hints in certain situations.
Hope this helps.
Dan Guzman
SQL Server MVP
"Vikram" <Vikram@.discussions.microsoft.com> wrote in message
news:BC820B12-579C-49A8-918F-3CD10973C270@.microsoft.com...
>I have a java web based application, which was build and designed keeping
> oracle in mind. The application also used a 3rd party API "cocobase" which
> used to do most of the data interraction. But there were many issue after
> the
> application is ported to work with SQL database and the most prominant
> issue
> is a Deadlock issue. We need to resolve the deadlock issue first. I found
> out
> the deadlock is happening one particular table say 'tableX'
> I have tried the following.
> 1.
> Findings : There are Selects, Inserts, Delete and Updates hapenning in the
> same table (tableX) for numerous times. These DML statements were executed
> using cocobase calls. The Insert, Delete and Updates were targeted first
> as
> deadlock were happening mostly after an Insert or Update. Insert/Update
> was
> taking some time to execute.
> Action taken : The Insert/Delete/Updates were changed from cocobase calls
> to
> direct jdbc calls.
> Benefit : The time taken by cocobase to prepare the statement was saved.
> 2.
> Findings : After the change, the Insert/Updates were being executed as
> normal sql statements using direct TSQL e.g insert into table1 values
> ('abc',
> 123). Each time the query is executed the sql engine would compile the
> statement.
> Action taken : The Insert/Updates were changed to execute using prepared
> statements. e.g exec sp_executesql 'insert into table1 values (?, ?)',
> '@.p1
> varchar, @.p2 int', 'abc', 123
> Benefit : The statements used to execute faster. If the query is fired
> once,
> then the sql engine will not compile the statement the next time it is
> fired
> (even by other transactions) with different parameter values.
> 3.
> Findings : After the changes, the deadlock still persist, but this time
> the
> deadlock were occuring mostly on the sp_cursoropen or sp_cursorfetch
> execution. It was found the the select statement were opening cursors and
> the
> cursors were getting closed in the end while closing the connection
> object.
> The cursor or statements need to be closed right after use.
> Action taken : The "Select" statements were changed from cocobase to
> direct
> jdbc calls. Most of the cursors or statements pertaining to the product
> table
> were closed after use. There were 2 or 3 places where the changes couldn't
> be
> applied cause of the complexity in the code. The recordset object itself
> were
> being dynamically created and used in a baseclass.
> Benefit : The locking period of a resource is reduced, if the
> cursor/statement is closed after use.
> 4.
> Findings : Every select query was opening a cursor which used to lock the
> resource in the database. Using cursor was not necessary as in the
> application when a query is fired or when a data is fetched, it is ideally
> pertaining to one batch, which is not very huge. If we could avoid using
> cursor, the locking would reduce and would help us resolve the deadlock
> issue.
> Action taken : The "SelectMethod" in the connectionstring or URL was
> changed
> from "Cursor" to "Direct". But due to the changes, the application
> chrashed
> and the change didnt work. Hence it was reverted back.
> Benefit : None, as the changes didnt work for the application.
> 5.
> Findings : The other cocobase calls to their tables like "CB_TABLES",
> "CB_OBJECTS", "CB_FIELDS", "CB_CLAUSES" were taking much time to execute.
> There was a index on the OBJECTNAME field, but was a nonclustered index.
> Action taken : The existing nonclustered index was changed to a clustered
> index.
> Benefit : The query executed faster as initially the sql engine was doing
> a
> "table scan" and now it was doing a "cluster index seek".
> Is there anything else anyone suggest to handle and resolve the deadlock?
> Any advise would be appreciated.
> --
> Cheers
> --
>
sql

Deadlock issue (JAVA + SQL server)

I have a Java web based application, which was build and designed keeping
oracle in mind. The application also used a 3rd party API "cocobase" which
used to do most of the data interraction. But there were many issue after th
e
application is ported to work with SQL database and the most prominant issue
is a Deadlock issue. We need to resolve the deadlock issue first. I found ou
t
the deadlock is happening one particular table say 'tableX'
I have tried the following.
1.
Findings : There are Selects, Inserts, Delete and Updates hapenning in the
same table (tableX) for numerous times. These DML statements were executed
using cocobase calls. The Insert, Delete and Updates were targeted first as
deadlock were happening mostly after an Insert or Update. Insert/Update was
taking some time to execute.
Action taken : The Insert/Delete/Updates were changed from cocobase calls to
direct jdbc calls.
Benefit : The time taken by cocobase to prepare the statement was saved.
2.
Findings : After the change, the Insert/Updates were being executed as
normal sql statements using direct TSQL e.g insert into table1 values ('abc'
,
123). Each time the query is executed the sql engine would compile the
statement.
Action taken : The Insert/Updates were changed to execute using prepared
statements. e.g exec sp_executesql 'insert into table1 values (?, ?)', '@.p1
varchar, @.p2 int', 'abc', 123
Benefit : The statements used to execute faster. If the query is fired once,
then the sql engine will not compile the statement the next time it is fired
(even by other transactions) with different parameter values.
3.
Findings : After the changes, the deadlock still persist, but this time the
deadlock were occuring mostly on the sp_cursoropen or sp_cursorfetch
execution. It was found the the select statement were opening cursors and th
e
cursors were getting closed in the end while closing the connection object.
The cursor or statements need to be closed right after use.
Action taken : The "Select" statements were changed from cocobase to direct
jdbc calls. Most of the cursors or statements pertaining to the product tabl
e
were closed after use. There were 2 or 3 places where the changes couldn't b
e
applied cause of the complexity in the code. The recordset object itself wer
e
being dynamically created and used in a baseclass.
Benefit : The locking period of a resource is reduced, if the
cursor/statement is closed after use.
4.
Findings : Every select query was opening a cursor which used to lock the
resource in the database. Using cursor was not necessary as in the
application when a query is fired or when a data is fetched, it is ideally
pertaining to one batch, which is not very huge. If we could avoid using
cursor, the locking would reduce and would help us resolve the deadlock issu
e.
Action taken : The "SelectMethod" in the connectionstring or URL was changed
from "Cursor" to "Direct". But due to the changes, the application chrashed
and the change didnt work. Hence it was reverted back.
Benefit : None, as the changes didnt work for the application.
5.
Findings : The other cocobase calls to their tables like "CB_TABLES",
"CB_OBJECTS", "CB_FIELDS", "CB_CLAUSES" were taking much time to execute.
There was a index on the OBJECTNAME field, but was a nonclustered index.
Action taken : The existing nonclustered index was changed to a clustered
index.
Benefit : The query executed faster as initially the sql engine was doing a
"table scan" and now it was doing a "cluster index seek".
Is there anything else anyone suggest to handle and resolve the deadlock?
Any advise would be appreciated.
Cheers
--Hi
"Vikram" wrote:

> I have a Java web based application, which was build and designed keeping
> oracle in mind. The application also used a 3rd party API "cocobase" which
> used to do most of the data interraction. But there were many issue after
the
> application is ported to work with SQL database and the most prominant iss
ue
> is a Deadlock issue. We need to resolve the deadlock issue first. I found
out
> the deadlock is happening one particular table say 'tableX'
> I have tried the following.
> 1.
> Findings : There are Selects, Inserts, Delete and Updates hapenning in the
> same table (tableX) for numerous times. These DML statements were executed
> using cocobase calls. The Insert, Delete and Updates were targeted first a
s
> deadlock were happening mostly after an Insert or Update. Insert/Update wa
s
> taking some time to execute.
> Action taken : The Insert/Delete/Updates were changed from cocobase calls
to
> direct jdbc calls.
> Benefit : The time taken by cocobase to prepare the statement was saved.
> 2.
> Findings : After the change, the Insert/Updates were being executed as
> normal sql statements using direct TSQL e.g insert into table1 values ('ab
c',
> 123). Each time the query is executed the sql engine would compile the
> statement.
> Action taken : The Insert/Updates were changed to execute using prepared
> statements. e.g exec sp_executesql 'insert into table1 values (?, ?)', '@.p
1
> varchar, @.p2 int', 'abc', 123
> Benefit : The statements used to execute faster. If the query is fired onc
e,
> then the sql engine will not compile the statement the next time it is fir
ed
> (even by other transactions) with different parameter values.
> 3.
> Findings : After the changes, the deadlock still persist, but this time th
e
> deadlock were occuring mostly on the sp_cursoropen or sp_cursorfetch
> execution. It was found the the select statement were opening cursors and
the
> cursors were getting closed in the end while closing the connection object
.
> The cursor or statements need to be closed right after use.
> Action taken : The "Select" statements were changed from cocobase to direc
t
> jdbc calls. Most of the cursors or statements pertaining to the product ta
ble
> were closed after use. There were 2 or 3 places where the changes couldn't
be
> applied cause of the complexity in the code. The recordset object itself w
ere
> being dynamically created and used in a baseclass.
> Benefit : The locking period of a resource is reduced, if the
> cursor/statement is closed after use.
> 4.
> Findings : Every select query was opening a cursor which used to lock the
> resource in the database. Using cursor was not necessary as in the
> application when a query is fired or when a data is fetched, it is ideally
> pertaining to one batch, which is not very huge. If we could avoid using
> cursor, the locking would reduce and would help us resolve the deadlock is
sue.
> Action taken : The "SelectMethod" in the connectionstring or URL was chang
ed
> from "Cursor" to "Direct". But due to the changes, the application chrash
ed
> and the change didnt work. Hence it was reverted back.
> Benefit : None, as the changes didnt work for the application.
> 5.
> Findings : The other cocobase calls to their tables like "CB_TABLES",
> "CB_OBJECTS", "CB_FIELDS", "CB_CLAUSES" were taking much time to execute.
> There was a index on the OBJECTNAME field, but was a nonclustered index.
> Action taken : The existing nonclustered index was changed to a clustered
> index.
> Benefit : The query executed faster as initially the sql engine was doing
a
> "table scan" and now it was doing a "cluster index seek".
> Is there anything else anyone suggest to handle and resolve the deadlock?
> Any advise would be appreciated.
> --
> Cheers
> --
You don't give the version of SQL Server you are using? There are
enhancements in SQL Server 2005 that make these easier to track down and
diagnose.
If you process is actually being blocked and not deadlocked then check out:
http://support.microsoft.com/defaul...b/271509/EN-US/
If you have deadlocks it usually require two or more processes that trying
to access the same resources but in different orders. See
http://support.microsoft.com/kb/832524/en-us
John|||On May 16, 9:58 am, Vikram <Vik...@.discussions.microsoft.com> wrote:
> I have a Java web based application, which was build and designed keeping
> oracle in mind. The application also used a 3rd party API "cocobase" which
> used to do most of the data interraction. But there were many issue after
the
> application is ported to work with SQL database and the most prominant iss
ue
> is a Deadlock issue. We need to resolve the deadlock issue first. I found
out
> the deadlock is happening one particular table say 'tableX'
> I have tried the following.
> 1.
> Findings : There are Selects, Inserts, Delete and Updates hapenning in the
> same table (tableX) for numerous times. These DML statements were executed
> using cocobase calls. The Insert, Delete and Updates were targeted first a
s
> deadlock were happening mostly after an Insert or Update. Insert/Update wa
s
> taking some time to execute.
> Action taken : The Insert/Delete/Updates were changed from cocobase calls
to
> direct jdbc calls.
> Benefit : The time taken by cocobase to prepare the statement was saved.
> 2.
> Findings : After the change, the Insert/Updates were being executed as
> normal sql statements using direct TSQL e.g insert into table1 values ('ab
c',
> 123). Each time the query is executed the sql engine would compile the
> statement.
> Action taken : The Insert/Updates were changed to execute using prepared
> statements. e.g exec sp_executesql 'insert into table1 values (?, ?)', '@.p
1
> varchar, @.p2 int', 'abc', 123
> Benefit : The statements used to execute faster. If the query is fired onc
e,
> then the sql engine will not compile the statement the next time it is fir
ed
> (even by other transactions) with different parameter values.
> 3.
> Findings : After the changes, the deadlock still persist, but this time th
e
> deadlock were occuring mostly on the sp_cursoropen or sp_cursorfetch
> execution. It was found the the select statement were opening cursors and
the
> cursors were getting closed in the end while closing the connection object
.
> The cursor or statements need to be closed right after use.
> Action taken : The "Select" statements were changed from cocobase to direc
t
> jdbc calls. Most of the cursors or statements pertaining to the product ta
ble
> were closed after use. There were 2 or 3 places where the changes couldn't
be
> applied cause of the complexity in the code. The recordset object itself w
ere
> being dynamically created and used in a baseclass.
> Benefit : The locking period of a resource is reduced, if the
> cursor/statement is closed after use.
> 4.
> Findings : Every select query was opening a cursor which used to lock the
> resource in the database. Using cursor was not necessary as in the
> application when a query is fired or when a data is fetched, it is ideally
> pertaining to one batch, which is not very huge. If we could avoid using
> cursor, the locking would reduce and would help us resolve the deadlock is
sue.
> Action taken : The "SelectMethod" in the connectionstring or URL was chang
ed
> from "Cursor" to "Direct". But due to the changes, the application chrash
ed
> and the change didnt work. Hence it was reverted back.
> Benefit : None, as the changes didnt work for the application.
> 5.
> Findings : The other cocobase calls to their tables like "CB_TABLES",
> "CB_OBJECTS", "CB_FIELDS", "CB_CLAUSES" were taking much time to execute.
> There was a index on the OBJECTNAME field, but was a nonclustered index.
> Action taken : The existing nonclustered index was changed to a clustered
> index.
> Benefit : The query executed faster as initially the sql engine was doing
a
> "table scan" and now it was doing a "cluster index seek".
> Is there anything else anyone suggest to handle and resolve the deadlock?
> Any advise would be appreciated.
> --
> Cheers
> --
1. Are you using trasaction . If yes , try to make duration of
transaction execution short by removing unwanted statements from the
transaction
2. Eventhough you have created indexes sql server may go for table
scan . Check the execution plan and provide appropriate index hints
on tables like
from table with (index(indexname))
3. Use the tables in the same order in different transactions
4. What is the isolation level used . If it is SERIALIZABLE, check
whether READ COMMITED is OK for your logic
5. If you use components , default isolation level may be SERIALIZABLE
try to change it to READ COMMITED|||> Benefit : The query executed faster as initially the sql engine was doing
> a
> "table scan" and now it was doing a "cluster index seek".
> Is there anything else anyone suggest to handle and resolve the deadlock?
> Any advise would be appreciated.
In addition to John's suggestions, take a look at execution plans of other
queries to ensure data are accessed using seeks rather than scans. Scans
are notorious for contributing to deadlocking and blocking. Also, you might
need to add locking hints in certain situations.
Hope this helps.
Dan Guzman
SQL Server MVP
"Vikram" <Vikram@.discussions.microsoft.com> wrote in message
news:BC820B12-579C-49A8-918F-3CD10973C270@.microsoft.com...
>I have a Java web based application, which was build and designed keeping
> oracle in mind. The application also used a 3rd party API "cocobase" which
> used to do most of the data interraction. But there were many issue after
> the
> application is ported to work with SQL database and the most prominant
> issue
> is a Deadlock issue. We need to resolve the deadlock issue first. I found
> out
> the deadlock is happening one particular table say 'tableX'
> I have tried the following.
> 1.
> Findings : There are Selects, Inserts, Delete and Updates hapenning in the
> same table (tableX) for numerous times. These DML statements were executed
> using cocobase calls. The Insert, Delete and Updates were targeted first
> as
> deadlock were happening mostly after an Insert or Update. Insert/Update
> was
> taking some time to execute.
> Action taken : The Insert/Delete/Updates were changed from cocobase calls
> to
> direct jdbc calls.
> Benefit : The time taken by cocobase to prepare the statement was saved.
> 2.
> Findings : After the change, the Insert/Updates were being executed as
> normal sql statements using direct TSQL e.g insert into table1 values
> ('abc',
> 123). Each time the query is executed the sql engine would compile the
> statement.
> Action taken : The Insert/Updates were changed to execute using prepared
> statements. e.g exec sp_executesql 'insert into table1 values (?, ?)',
> '@.p1
> varchar, @.p2 int', 'abc', 123
> Benefit : The statements used to execute faster. If the query is fired
> once,
> then the sql engine will not compile the statement the next time it is
> fired
> (even by other transactions) with different parameter values.
> 3.
> Findings : After the changes, the deadlock still persist, but this time
> the
> deadlock were occuring mostly on the sp_cursoropen or sp_cursorfetch
> execution. It was found the the select statement were opening cursors and
> the
> cursors were getting closed in the end while closing the connection
> object.
> The cursor or statements need to be closed right after use.
> Action taken : The "Select" statements were changed from cocobase to
> direct
> jdbc calls. Most of the cursors or statements pertaining to the product
> table
> were closed after use. There were 2 or 3 places where the changes couldn't
> be
> applied cause of the complexity in the code. The recordset object itself
> were
> being dynamically created and used in a baseclass.
> Benefit : The locking period of a resource is reduced, if the
> cursor/statement is closed after use.
> 4.
> Findings : Every select query was opening a cursor which used to lock the
> resource in the database. Using cursor was not necessary as in the
> application when a query is fired or when a data is fetched, it is ideally
> pertaining to one batch, which is not very huge. If we could avoid using
> cursor, the locking would reduce and would help us resolve the deadlock
> issue.
> Action taken : The "SelectMethod" in the connectionstring or URL was
> changed
> from "Cursor" to "Direct". But due to the changes, the application
> chrashed
> and the change didnt work. Hence it was reverted back.
> Benefit : None, as the changes didnt work for the application.
> 5.
> Findings : The other cocobase calls to their tables like "CB_TABLES",
> "CB_OBJECTS", "CB_FIELDS", "CB_CLAUSES" were taking much time to execute.
> There was a index on the OBJECTNAME field, but was a nonclustered index.
> Action taken : The existing nonclustered index was changed to a clustered
> index.
> Benefit : The query executed faster as initially the sql engine was doing
> a
> "table scan" and now it was doing a "cluster index seek".
> Is there anything else anyone suggest to handle and resolve the deadlock?
> Any advise would be appreciated.
> --
> Cheers
> --
>

dbo login not mapped to sa

Hi,
I have a web task in SQL 7 that is failing and I think it
might have to do with the fact that dbo isn't mapped to sa
in the users of the database, can anyone confirm? I have
recently moved this database into a hardware clustered
environment, I think it may have some to do with the
restore.
Thanks
MegHi,
The stored proc sp_change_users_login exists in sql 7, but
when using it to tie together dbo and sa I get the
following error message "Server: Msg 15287, Level 16,
State 1, Procedure sp_change_users_login, Line 33
Terminating this procedure. 'sa' is a forbidden value for
the login name parameter in this procedure.". I am going
to have a look on technet and the like for this error, but
if anyone has any other suggestions, they are welcome!
Thanks
Meg
>--Original Message--
>Hi,
>I have a web task in SQL 7 that is failing and I think it
>might have to do with the fact that dbo isn't mapped to
sa
>in the users of the database, can anyone confirm? I have
>recently moved this database into a hardware clustered
>environment, I think it may have some to do with the
>restore.
>Thanks
>Meg
>.
>|||The dbo user is a special case regarding login mapping. Database
ownership determines the login mapped to the dbo user. You can use
sp_changedbowner to specify the desired owner. This should be standard
procedure after a restore or attach from another server:
USE MyDatabase
EXEC sp_changedbowner 'sa'
GO
In some cases, you may get the ambiguous error 'the proposed owner is
already a user in the database'. In this case, you can workaround the
problem by temporarily changing ownership to a non-conflicting login:
USE MyDatabase
EXEC sp_addlogin 'TempOwner'
EXEC sp_changedbowner 'TempOwner'
EXEC sp_changedbowner 'sa'
EXEC sp_droplogin 'TempOwner'
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"meg" <meg.neeson@.barclays.co.uk> wrote in message
news:09f201c35db3$87af5610$a401280a@.phx.gbl...
> Hi,
> The stored proc sp_change_users_login exists in sql 7, but
> when using it to tie together dbo and sa I get the
> following error message "Server: Msg 15287, Level 16,
> State 1, Procedure sp_change_users_login, Line 33
> Terminating this procedure. 'sa' is a forbidden value for
> the login name parameter in this procedure.". I am going
> to have a look on technet and the like for this error, but
> if anyone has any other suggestions, they are welcome!
> Thanks
> Meg
> >--Original Message--
> >Hi,
> >
> >I have a web task in SQL 7 that is failing and I think it
> >might have to do with the fact that dbo isn't mapped to
> sa
> >in the users of the database, can anyone confirm? I have
> >recently moved this database into a hardware clustered
> >environment, I think it may have some to do with the
> >restore.
> >
> >Thanks
> >Meg
> >.
> >

DBNULL is giving me a headach!

Could someone please help with this:
I'm trying to connect to a sql server 2000 that to web service. When i run
the service i get:
System.InvalidCastException: Specified cast is not valid.
at wsStudents.Service1.GetStudent(String studentlastname) in
C:\Inetpub\wwwroot\wsStudents\Service1.asmx.vb:line 82
and a throws a similar error (Cast from type 'DBNull' to type 'String' is
not valid) when i try to consume it. This code accepts one input and one
output paramer. The value returned from the output paramter seems be Nothing
"
!!
Here's the code:
<WebMethod()> _
Public Function GetStudent(ByVal studentlastname As String) As String
Dim dsResult As New DataSet
Dim strSQL As String
Dim result_outParam As String
'strSQL = "SELECT @.StuFee FROM dbo.Student WHERE
dbo.Student.LastName = @.StuLName and dbo.Student.FirstName =@.StuFName"
strSQL = "SELECT @.StuFee FROM dbo.Student WHERE dbo.Student.LastName
= @.StuLName"
Dim cmd As New SqlCommand(strSQL, conn)
'Add the first input paramter
cmd.Parameters.Add("@.StuLName", SqlDbType.VarChar)
cmd.Parameters("@.StuLName").Direction = ParameterDirection.Input
cmd.Parameters("@.StuLName").Value = studentlastname
'Add the output paramter
cmd.Parameters.Add("@.StuFee", SqlDbType.VarChar, 50)
cmd.Parameters("@.StuFee").Direction = ParameterDirection.Output
If cmd.Connection.State <> ConnectionState.Open Then
conn.Open()
End If
cmd.ExecuteNonQuery()
result_outParam = cmd.Parameters("@.StuFee").Value
Return result_outParam
conn.Close()
I tried to rewrite the last code like this:
result_outParam =
System.DBNull.Value.ToString(cmd.Parameters("@.StuFee").Value)
but no luck
UKok, that is not possible if the value returned is DBNull.
result_outParam = cmd.Parameters("@.StuFee").Value == DBNull.Value ?
string.Empty : cmd.Parameters("@.StuFee").Value.ToString();
This is the long way :-)
HTH, Jens Suessmeyer.|||Thanks Jens
This suggested code i assume is in C# (mine was in vb .net). I'm not
familiar with using "?" symbol. Can you please translate this into VB .net.
Thanks. Execuse my ignorance.
Is this suggested code one line or two lines:
result_outParam = cmd.Parameters("@.StuFee").Value == DBNull.Value ?
string.Empty : cmd.Parameters("@.StuFee").Value.ToString()
UK
"Jens" wrote:

> ok, that is not possible if the value returned is DBNull.
> result_outParam = cmd.Parameters("@.StuFee").Value == DBNull.Value ?
> string.Empty : cmd.Parameters("@.StuFee").Value.ToString();
> This is the long way :-)
> HTH, Jens Suessmeyer.
>|||Nab (Nab@.discussions.microsoft.com) writes:
> This suggested code i assume is in C# (mine was in vb .net). I'm not
> familiar with using "?" symbol. Can you please translate this into VB
> .net.
?: is the C# equivalent of the Iif function in Visual Basic. That is
condition ? true-return : false-return
So Jens's example would read:
result_outParam = Iif(cmd.Parameters("@.StuFee").Value == DBNull.Value, _
string.Empty, _
cmd.Parameters("@.StuFee").Value.ToString())
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|||Thanks. i'll try this and let you know. What I don't understand is that
sometime ago i wrote similar code but never had to to deal with an issue lik
e
this.
I had deleted SQL Servre 2000 since and re-installed it again but never
installed any service packs this time. Could this have something to do with
it?
"Erland Sommarskog" wrote:

> Nab (Nab@.discussions.microsoft.com) writes:
> ?: is the C# equivalent of the Iif function in Visual Basic. That is
> condition ? true-return : false-return
> So Jens's example would read:
> result_outParam = Iif(cmd.Parameters("@.StuFee").Value == DBNull.Value,
_
> string.Empty, _
> cmd.Parameters("@.StuFee").Value.ToString())
>
> --
> 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
>|||
"Erland Sommarskog" wrote:

> Nab (Nab@.discussions.microsoft.com) writes:
> ?: is the C# equivalent of the Iif function in Visual Basic. That is
> condition ? true-return : false-return
> So Jens's example would read:
> result_outParam = Iif(cmd.Parameters("@.StuFee").Value == DBNull.Value,
_
> string.Empty, _
> cmd.Parameters("@.StuFee").Value.ToString())
>
> --
> 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 what others have posted...
I encounter this often enough that I created a function that I call whenever
I retrieve a value from the database in VB. It saves the time of having to
write an IF statement every time you need to retrieve a value from the
database.
After you have the function your code would read:
result_outParam = nvl(cmd.Parameters("@.StuFee").Value)
Public Shared Function nvl(ByVal strValue As Object) As String
'***************************************
********************************
' Public method
' returns empty strign in place of DB nulls
' prevents errors caused by type discrepencies
'***************************************
********************************
If strValue Is DBNull.Value Then
Return ""
Else
Return strValue
End If
End Function
"Nab" <Nab@.discussions.microsoft.com> wrote in message
news:AC678A4A-BE76-4884-AA65-8D1BFB3697CB@.microsoft.com...
> Could someone please help with this:
>
> I'm trying to connect to a sql server 2000 that to web service. When i
run
> the service i get:
> System.InvalidCastException: Specified cast is not valid.
> at wsStudents.Service1.GetStudent(String studentlastname) in
> C:\Inetpub\wwwroot\wsStudents\Service1.asmx.vb:line 82
> and a throws a similar error (Cast from type 'DBNull' to type 'String' is
> not valid) when i try to consume it. This code accepts one input and one
> output paramer. The value returned from the output paramter seems be
Nothing"
> !!
> Here's the code:
> <WebMethod()> _
> Public Function GetStudent(ByVal studentlastname As String) As String
> Dim dsResult As New DataSet
> Dim strSQL As String
> Dim result_outParam As String
> 'strSQL = "SELECT @.StuFee FROM dbo.Student WHERE
> dbo.Student.LastName = @.StuLName and dbo.Student.FirstName =@.StuFName"
> strSQL = "SELECT @.StuFee FROM dbo.Student WHERE
dbo.Student.LastName
> = @.StuLName"
> Dim cmd As New SqlCommand(strSQL, conn)
> 'Add the first input paramter
> cmd.Parameters.Add("@.StuLName", SqlDbType.VarChar)
> cmd.Parameters("@.StuLName").Direction = ParameterDirection.Input
> cmd.Parameters("@.StuLName").Value = studentlastname
> 'Add the output paramter
> cmd.Parameters.Add("@.StuFee", SqlDbType.VarChar, 50)
> cmd.Parameters("@.StuFee").Direction = ParameterDirection.Output
> If cmd.Connection.State <> ConnectionState.Open Then
> conn.Open()
> End If
> cmd.ExecuteNonQuery()
> result_outParam = cmd.Parameters("@.StuFee").Value
> Return result_outParam
> conn.Close()
> I tried to rewrite the last code like this:
> result_outParam =
> System.DBNull.Value.ToString(cmd.Parameters("@.StuFee").Value)
> but no luck
> --
> UK|||Nab (Nab@.discussions.microsoft.com) writes:
> Thanks. i'll try this and let you know. What I don't understand is that
> sometime ago i wrote similar code but never had to to deal with an issue
> like this.
> I had deleted SQL Servre 2000 since and re-installed it again but never
> installed any service packs this time. Could this have something to do
> with it?
The installation of SQL 2000 cannot affect ADO .Net or VB .Net, as they
are separate products. Maybe you didn't have to deal with any NULL values
the first time?
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|||Thanks for all your replies. Jim's function-based code seems to have worked
better for me!
Cheers.
--
UK
"Jim Underwood" wrote:

> In addition to what others have posted...
> I encounter this often enough that I created a function that I call whenev
er
> I retrieve a value from the database in VB. It saves the time of having t
o
> write an IF statement every time you need to retrieve a value from the
> database.
> After you have the function your code would read:
> result_outParam = nvl(cmd.Parameters("@.StuFee").Value)
> Public Shared Function nvl(ByVal strValue As Object) As String
> '***************************************
********************************
> ' Public method
> ' returns empty strign in place of DB nulls
> ' prevents errors caused by type discrepencies
> '***************************************
********************************
> If strValue Is DBNull.Value Then
> Return ""
> Else
> Return strValue
> End If
> End Function
>
> "Nab" <Nab@.discussions.microsoft.com> wrote in message
> news:AC678A4A-BE76-4884-AA65-8D1BFB3697CB@.microsoft.com...
> run
> Nothing"
> dbo.Student.LastName
>
>|||Apologies. In fact, all suggested solutions worked fine now. Thanks to all.
--
UK
"Jim Underwood" wrote:

> In addition to what others have posted...
> I encounter this often enough that I created a function that I call whenev
er
> I retrieve a value from the database in VB. It saves the time of having t
o
> write an IF statement every time you need to retrieve a value from the
> database.
> After you have the function your code would read:
> result_outParam = nvl(cmd.Parameters("@.StuFee").Value)
> Public Shared Function nvl(ByVal strValue As Object) As String
> '***************************************
********************************
> ' Public method
> ' returns empty strign in place of DB nulls
> ' prevents errors caused by type discrepencies
> '***************************************
********************************
> If strValue Is DBNull.Value Then
> Return ""
> Else
> Return strValue
> End If
> End Function
>
> "Nab" <Nab@.discussions.microsoft.com> wrote in message
> news:AC678A4A-BE76-4884-AA65-8D1BFB3697CB@.microsoft.com...
> run
> Nothing"
> dbo.Student.LastName
>
>

Dbnetlib connectionopen(preloginhandshake()) error

SQL 2000/SQL 2005(see below) running on Windows 2003 sp1, web server running
IIS 5.0 on Windows 2000 sp4.
Sometime in the first week of every month since at least Nov 2005, I get an
Dbnetlib connectionopen(preloginhandshake()) error. Connections to the
databases from the Web pages will be slow and intermittent. Rebooting both
web server and sql machine does not fix the error.
Upgraded to SQL 2005 in Jan 06. The connection issue will last 24 to 36
hours then fix itself. Any suggestions on how to fix the error would be
appreciated.
ThanksDuring that time the system is acting as described, I would run a SQL
profiler trace to rule out SQL Server jobs/users. If not found, then run
Windows performance monitor looking at memory and network traffic.
Let me know where I can help.
Adam
"Heather" wrote:

> SQL 2000/SQL 2005(see below) running on Windows 2003 sp1, web server runni
ng
> IIS 5.0 on Windows 2000 sp4.
> Sometime in the first week of every month since at least Nov 2005, I get a
n
> Dbnetlib connectionopen(preloginhandshake()) error. Connections to the
> databases from the Web pages will be slow and intermittent. Rebooting both
> web server and sql machine does not fix the error.
> Upgraded to SQL 2005 in Jan 06. The connection issue will last 24 to 36
> hours then fix itself. Any suggestions on how to fix the error would be
> appreciated.
> Thanks
>

Dbnetlib connectionopen(preloginhandshake()) error

SQL 2000/SQL 2005(see below) running on Windows 2003 sp1, web server running
IIS 5.0 on Windows 2000 sp4.
Sometime in the first week of every month since at least Nov 2005, I get an
Dbnetlib connectionopen(preloginhandshake()) error. Connections to the
databases from the Web pages will be slow and intermittent. Rebooting both
web server and sql machine does not fix the error.
Upgraded to SQL 2005 in Jan 06. The connection issue will last 24 to 36
hours then fix itself. Any suggestions on how to fix the error would be
appreciated.
Thanks
During that time the system is acting as described, I would run a SQL
profiler trace to rule out SQL Server jobs/users. If not found, then run
Windows performance monitor looking at memory and network traffic.
Let me know where I can help.
Adam
"Heather" wrote:

> SQL 2000/SQL 2005(see below) running on Windows 2003 sp1, web server running
> IIS 5.0 on Windows 2000 sp4.
> Sometime in the first week of every month since at least Nov 2005, I get an
> Dbnetlib connectionopen(preloginhandshake()) error. Connections to the
> databases from the Web pages will be slow and intermittent. Rebooting both
> web server and sql machine does not fix the error.
> Upgraded to SQL 2005 in Jan 06. The connection issue will last 24 to 36
> hours then fix itself. Any suggestions on how to fix the error would be
> appreciated.
> Thanks
>