Deadlock in Access through ODBC 
Author Message
 Deadlock in Access through ODBC

Hi, I'm using Access as the main database for a product I'm making.

The problem: I use ODBC to access the .MBD. I use multiple threads(3) which
do lots of SQLFetch and SQLExecDirect with
lots of UPDATE, INSERT, DELETE and SELECT statements. Once in a while
though, about twice a day, it deadlocks. Is
Access fully thread safe? Or should I myself synchronize the process (and
lose speed when dealing with Oracle or
SQL Server through ODBC)? Below is the debug information I got from the
deadlock.

Thanks!

----------------------------------------------------------------------------
---------------------------------------
-- The deadlocked thread's stack walk.

ZwReplyWaitSendChannel
77F68250 NtWaitForSingleObject  <  77F6825B  <  77F68260 NtWaitHighEventPair
 AddrPC 77F6825B  AddrReturn 77F04F97  AddrFrame 0962FC2C  AddrStack
0962FC50
 Params 00000088 FFFFFFFF 00000000 04C07DB9

CreateRemoteThread      [in Kernel32.DLL]
77F04F88 WaitForSingleObject   <  77F04F97  <  77F04F9A CreateRemoteThread
 AddrPC 77F04F97  AddrReturn 6A997620  AddrFrame 0962FC2C  AddrStack
0962FCE4
 Params 00000001 02520EBC FFFFFFFD 00000000

SQLDriversW                    [in ODBC32.DLL]
6A99756A SQLExecDirect         <  6A997620  <  6A99762A SQLCancel
 AddrPC 6A997620  AddrReturn 00433BC5  AddrFrame 0962FC2C  AddrStack
0962FD0C
 Params 012A0000 02520EBC FFFFFFFD 0BAD38B0

ObjectTimer::ObjectInList-- my function


 AddrPC 00433BC5  AddrReturn 004107B9  AddrFrame 0962FC2C  AddrStack
0962FEC0
 Params 02520EBC 0B292448 09EE8708 09EE5590

-- The logged entries right before the deadlock was detected.

(Warning),11/23/1998,17:51:26,SQLExecDirect failed, trying to reconnect.
 SQL ERROR [SQLConnect]:
 SQL State: S1000
 Native Error: 4294966194
 Error message: [Microsoft][ODBC Microsoft Access 97 Driver] Couldn't
update; currently locked by user 'admin' on machine 'MACHINE'.

[ 3 seconds gap]

(CRITICAL),11/23/1998,17:51:29,Unable to connect to ODBC data source name
T4C Server, accounting will fail.
 SQL ERROR [SQLExecDirect]:
 Original SQL query statement: [ DELETE FROM Boosts WHERE BaseOwnerID=412 ]
 SQL State: S1000
 Native Error: 4294966194
 Error message: [Microsoft][ODBC Microsoft Access 97 Driver] Couldn't
update; currently locked by user 'admin' on machine 'MACHINE'.
-- Deadlock detected.



Mon, 14 May 2001 03:00:00 GMT  
 Deadlock in Access through ODBC
I have had the same problem on a dual processor machine. Set the affinity so
access uses one processor.

--
Replace 'NOSPAM' with 'oscarfab' to reply via e-mail.
----------

Quote:

>Hi, I'm using Access as the main database for a product I'm making.

>The problem: I use ODBC to access the .MBD. I use multiple threads(3) which
>do lots of SQLFetch and SQLExecDirect with
>lots of UPDATE, INSERT, DELETE and SELECT statements. Once in a while
>though, about twice a day, it deadlocks. Is
>Access fully thread safe? Or should I myself synchronize the process (and
>lose speed when dealing with Oracle or
>SQL Server through ODBC)? Below is the debug information I got from the
>deadlock.

>Thanks!

>---------------------------------------------------------------------------
-
>---------------------------------------
>-- The deadlocked thread's stack walk.

>ZwReplyWaitSendChannel
>77F68250 NtWaitForSingleObject  <  77F6825B  <  77F68260
NtWaitHighEventPair
> AddrPC 77F6825B  AddrReturn 77F04F97  AddrFrame 0962FC2C  AddrStack
>0962FC50
> Params 00000088 FFFFFFFF 00000000 04C07DB9

>CreateRemoteThread      [in Kernel32.DLL]
>77F04F88 WaitForSingleObject   <  77F04F97  <  77F04F9A CreateRemoteThread
> AddrPC 77F04F97  AddrReturn 6A997620  AddrFrame 0962FC2C  AddrStack
>0962FCE4
> Params 00000001 02520EBC FFFFFFFD 00000000

>SQLDriversW                    [in ODBC32.DLL]
>6A99756A SQLExecDirect         <  6A997620  <  6A99762A SQLCancel
> AddrPC 6A997620  AddrReturn 00433BC5  AddrFrame 0962FC2C  AddrStack
>0962FD0C
> Params 012A0000 02520EBC FFFFFFFD 0BAD38B0

>ObjectTimer::ObjectInList-- my function


> AddrPC 00433BC5  AddrReturn 004107B9  AddrFrame 0962FC2C  AddrStack
>0962FEC0
> Params 02520EBC 0B292448 09EE8708 09EE5590

>-- The logged entries right before the deadlock was detected.

>(Warning),11/23/1998,17:51:26,SQLExecDirect failed, trying to reconnect.
> SQL ERROR [SQLConnect]:
> SQL State: S1000
> Native Error: 4294966194
> Error message: [Microsoft][ODBC Microsoft Access 97 Driver] Couldn't
>update; currently locked by user 'admin' on machine 'MACHINE'.

>[ 3 seconds gap]

>(CRITICAL),11/23/1998,17:51:29,Unable to connect to ODBC data source name
>T4C Server, accounting will fail.
> SQL ERROR [SQLExecDirect]:
> Original SQL query statement: [ DELETE FROM Boosts WHERE BaseOwnerID=412 ]
> SQL State: S1000
> Native Error: 4294966194
> Error message: [Microsoft][ODBC Microsoft Access 97 Driver] Couldn't
>update; currently locked by user 'admin' on machine 'MACHINE'.
>-- Deadlock detected.



Mon, 14 May 2001 03:00:00 GMT  
 Deadlock in Access through ODBC

Quote:
>I have had the same problem on a dual processor machine. Set the affinity
so
>access uses one processor.

Ouch! ok, I'll try it.


Mon, 14 May 2001 03:00:00 GMT  
 Deadlock in Access through ODBC
Did the change on all threads which access SQL functions and I still get the
crashes. As far as I know though, ODBC will itself spawn 3 threads inside
the process, do I have access to know their handles (could try to set their
affinity mask too)?

Quote:
>I have had the same problem on a dual processor machine. Set the affinity
so
>access uses one processor.



Tue, 15 May 2001 03:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Help: Deadlocks using SQL ODBC driver in ISAPI

2. ODBC call's to Close() causes deadlocks??!!

3. ODBC and Deadlock Victim

4. DeadLock while CRecordset open Access database

5. Strange ODBC errors using DAO to access linked tables in Access .MDB

6. Accessing access via MFC Database classes and ODBC

7. Accessing an Ms Access 2000 Query using VC++, via ODBC

8. Accessing access via MFC Database classes and ODBC

9. Accessing access via MFC Database classes and ODBC

10. ODBC .mdb Database access

11. Problems with the ODBC driver of MS Access

12. Convert function and ODBC (MS Access)

 

 
Powered by phpBB® Forum Software