DAO, Transactions, SQLServer
From: SerGioGio (sergiogio_at_yahoo.fr)
Date: 09/28/04
- Next message: Mike Epprecht (SQL MVP): "RE: DAO, Transactions, SQLServer"
- Previous message: SAI: "client server programming"
- Next in thread: Mike Epprecht (SQL MVP): "RE: DAO, Transactions, SQLServer"
- Reply: Mike Epprecht (SQL MVP): "RE: DAO, Transactions, SQLServer"
- Reply: Victor Koch: "Re: DAO, Transactions, SQLServer"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 28 Sep 2004 09:43:19 +0200
Hello,
I have a problem with DAO, Transactions and SQLServer. I want to do a very
simple thing (in VB)!
BeginTrans
Call db.Execute("UPDATE counter SET value = value + 1 WHERE name =
'mycounter'", dbSQLPassThrough)
Set Rec = db.OpenRecordSet("SELECT value FROM counter WHERE name =
'mycounter'", dbOpenSnapshot, dbSQLPassThrough)
value = Rec(0)
CommitTrans
Note how simple this is. I just want to get a new value for a counter,
UPDATing first in order to make sure each value is returned only once, even
in concurrent environment. This is what you learn in school.
Well, this code works in Oracle, Sybase, in SQLServer using OSQL, but not in
SQLServer using DAO/ODBC.
In SQLServer using DAO/ODBC the code just hangs the entire application at
the SELECT line.
I believe the problem comes from the ODBC SQL Server driver
(2000.85.1025.00). From SQLServer OSQL I can see that multiple connections
are opened (by the driver I believe) and I suspect that the driver sends the
UPDATE statement to connection 1 for instance and the SELECT query to ...
connection 2! Of course this will cause a deadlock.
I saw microsoft comments
http://support.microsoft.com/default.aspx?scid=kb;EN-US;170548 , but I am
not really using JET, since all my database calls use the dbSQLPassThrough
option. I also tried to disable the ODBC connection pool but this is
useless. I believe it really is the Drivers fault since I can get these
statements to work in Sybase and Oracle.
This is the kind of stuff that puzzles me the most. The whole microsoft
architecture tries to be smarter than you are and takes control of
everything, but fails to do the simplest things AND it really seems you
cannot disable it.
Any help would be very much appreciated.
SerGioGio
- Next message: Mike Epprecht (SQL MVP): "RE: DAO, Transactions, SQLServer"
- Previous message: SAI: "client server programming"
- Next in thread: Mike Epprecht (SQL MVP): "RE: DAO, Transactions, SQLServer"
- Reply: Mike Epprecht (SQL MVP): "RE: DAO, Transactions, SQLServer"
- Reply: Victor Koch: "Re: DAO, Transactions, SQLServer"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
