SQL Server transaction problem

  • Thread starter Thread starter nick
  • Start date Start date
N

nick

I am writing a SQL server table and Ole Jet dbf table. They are in a SQL
transaction. However, writing dbf file (on network driver) sometimes halts
and I must close the browser (or in VS.Net when debug). After that, the
table lock of SQL server remains. I must go to SQL enterprise manager to
manully kill the process.

Looks the problem is caused by a started transaction termination without
commit or rollback. Any way to make SQL Server automatically release lock
(or roll back) after a period of time if the application is killed in the
middle of transaction?


btw, how to make OLE db dbase quit if network driver of the dbf files has
problem?
 
See the WAITFOR T-SQL statement in SQL Books Online. FWIW, starting an
explicit transaction in SQL Server that depends on an external,
file-based application completing successfully is a really, really bad
idea. Even with a waitfor, this is going to kill performance and
introduce concurrency conflicts unless you only have a single-user SQL
Server. I'd recommend rethinking your approach because this one is
never going to work reliably.

--Mary
 
IC, thanks, I did this because I need to insert a row in SQL Server and dbf
file (OLEDB Jet) at the same time and I want to make it atomic. Maybe I just
code consistent checking myself(delete the inserted one if another one
failed). any better solution?
 
Also, can I say it's not a good idea to use transaction cross
different/external-databases (or different db server)? Since it will involve
other issues such as networking, file system, etc and cause concurrent
problem?
 
Where does the requirement come in that they have to be at the exact
same time? If that were really true, you wouldn't be using a DBF
table, it would all be server-based. Doing them sequentially makes a
lot more sense. I'd try the file-based transaction first, get back a
success/fail, then do the SQL Server transaction on success. On fail,
nothing lost, no impact on the server. You don't want to hold locks on
the server waiting for your file-based transaction to complete or roll
back, you want to keep any transactions as short as possible.

--Mary
 
Absolutely.

--Mary

Also, can I say it's not a good idea to use transaction cross
different/external-databases (or different db server)? Since it will involve
other issues such as networking, file system, etc and cause concurrent
problem?
 
Back
Top