SQL Server transaction problem

[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?

 

[Mary Chipman]

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

 

[nick]

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?

 

[nick]

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?

 

[Mary Chipman]

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

 

[Mary Chipman]

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?