deadlocks when fetching

Hi!
We are developing cgi applications, using ODBC and (among else) MSSQL.
Now we are experiencing deadlocks on MSSQL only, and the problem is -
they occur not when the query is submitted, but when rows are fetched.
We don't use transactions with several statements, so basically every
query/update/insert is in its own transaction. This is because we need
to be compatible with transaction less database systems. Because of
this, we never encounter deadlocks either, except on MSSQL. It seems
that sometimes a select gets in the way of an update in progress or
something, and the select transaction is terminated. We handle this in
our app by just re-issuing the query if a deadlock was detected on query
submittal. However, when the deadlock occurs not when the query is sent,
but when the records are fetched, handling the event becomes much
harder. We'd rather have the query fail right away, than in the middle
of fetching rows.
Any ideas how to accomplish this, using either query hints, session
settings, etc?
Regards,
Erik KnudsenIt sounds like you are using a server side cursor. Have you tried using a
client side cursor instead?
Andrew J. Kelly SQL MVP
"Erik Knudsen" <erikk@.c2i.net> wrote in message
news:eHl7B$YHFHA.3076@.tk2msftngp13.phx.gbl...
> Hi!
> We are developing cgi applications, using ODBC and (among else) MSSQL.
> Now we are experiencing deadlocks on MSSQL only, and the problem is - they
> occur not when the query is submitted, but when rows are fetched.
> We don't use transactions with several statements, so basically every
> query/update/insert is in its own transaction. This is because we need to
> be compatible with transaction less database systems. Because of this, we
> never encounter deadlocks either, except on MSSQL. It seems that sometimes
> a select gets in the way of an update in progress or something, and the
> select transaction is terminated. We handle this in our app by just
> re-issuing the query if a deadlock was detected on query submittal.
> However, when the deadlock occurs not when the query is sent, but when the
> records are fetched, handling the event becomes much harder. We'd rather
> have the query fail right away, than in the middle of fetching rows.
> Any ideas how to accomplish this, using either query hints, session
> settings, etc?
>
> Regards,
> Erik Knudsen|||Erik
http://www.sql-server-performance.com/deadlocks.asp
"Erik Knudsen" <erikk@.c2i.net> wrote in message
news:eHl7B$YHFHA.3076@.tk2msftngp13.phx.gbl...
> Hi!
> We are developing cgi applications, using ODBC and (among else) MSSQL.
> Now we are experiencing deadlocks on MSSQL only, and the problem is -
> they occur not when the query is submitted, but when rows are fetched.
> We don't use transactions with several statements, so basically every
> query/update/insert is in its own transaction. This is because we need
> to be compatible with transaction less database systems. Because of
> this, we never encounter deadlocks either, except on MSSQL. It seems
> that sometimes a select gets in the way of an update in progress or
> something, and the select transaction is terminated. We handle this in
> our app by just re-issuing the query if a deadlock was detected on query
> submittal. However, when the deadlock occurs not when the query is sent,
> but when the records are fetched, handling the event becomes much
> harder. We'd rather have the query fail right away, than in the middle
> of fetching rows.
> Any ideas how to accomplish this, using either query hints, session
> settings, etc?
>
> Regards,
> Erik Knudsen|||Andrew J. Kelly wrote:

> It sounds like you are using a server side cursor. Have you tried using a
> client side cursor instead?
We basically use (ODBC):
--
1. SQLAllocStmt()
2. SQLExecDirect()
3. SQLFetch() (repetedly)
4. SQLFreeStmt()
--
We don't explicitly use cursors, and we don't want to because some of
our supported DBMS's don't support it.
I'd like to force the deadlock to happen at SQLExecDirect() or not at
all. I guess that would involve setting some stricter (not as
optimistic) locking policy or something. Any ideas?
Regards,
Erik Knudsen|||I am not familiar with programming at the base ODBC level so I may be
incorrect here but it seems to me if you are doing a Fetch you are using a
cursor. You can run profiler to see exactly what kinds of statements are
being executed on sql servers end. I am willing to bet you will see
sp_cursorpreparem sp_cursorfetch etc. and that usually indicates a server
side cursor. What isolation level are you running under? Make sure
something is not setting it to serializable and don't assume it is not.
Again you can check with profiler to see if there is a different isolation
level being set.
Andrew J. Kelly SQL MVP
"Erik Knudsen" <erikk@.c2i.net> wrote in message
news:%23oe2mrZHFHA.2356@.TK2MSFTNGP12.phx.gbl...
> Andrew J. Kelly wrote:
>
> We basically use (ODBC):
> --
> 1. SQLAllocStmt()
> 2. SQLExecDirect()
> 3. SQLFetch() (repetedly)
> 4. SQLFreeStmt()
> --
> We don't explicitly use cursors, and we don't want to because some of our
> supported DBMS's don't support it.
> I'd like to force the deadlock to happen at SQLExecDirect() or not at all.
> I guess that would involve setting some stricter (not as optimistic)
> locking policy or something. Any ideas?
>
> Regards,
> Erik Knudsen|||Erik Knudsen wrote:
> Andrew J. Kelly wrote:
>
> We basically use (ODBC):
> --
> 1. SQLAllocStmt()
> 2. SQLExecDirect()
> 3. SQLFetch() (repetedly)
> 4. SQLFreeStmt()
> --
> We don't explicitly use cursors, and we don't want to because some of
> our supported DBMS's don't support it.
> I'd like to force the deadlock to happen at SQLExecDirect() or not at
> all. I guess that would involve setting some stricter (not as
> optimistic) locking policy or something. Any ideas?
>
> Regards,
> Erik Knudsen
Not sure if this is related, but I believe SQLAllocStmt() has been
replaced with SQLAllocHandle(). In addition SQLFreeStmt() is not
recommended in ODBC 3.0 and later (replaced with SQLFreeHandle I think).
But as Andrew mentioned, check your cursor type. probably fastest to use
a forward-only, read-only, firehose cursor with ODBC. These types of
"cursors" are not cursors in the strictest sense. They are analogous to
SQL Server generating a result set (as it normally does) and pulling
that information into the application using ODBC.
David Gugick
Imceda Software
www.imceda.com