Network failure

[adasoft]

Hi,
We r developing enterprise solution that's why we need inconsistence database operations.My problem is related with the transaction which are uncompleted.Suppose I have three networks(N1,N2,N3) and having separate databases for each network.A user is logging from N1 and initiated a transaction for N2 database.At that time network fails and the N1 is out.The resources are locked due to transaction.If network is up then the table referenced in transaction is not reachable.No one can access those tables.What can we do to release these resources?I mean how can we rollback those transactions that were uncompleted due to network failure.

Thanx in advance

 

[ks2000]

hi,
Which Language you are using as front end ??
if you are using VB , you can handle that net work error
and in that error handler process you can use roll back transaction ,

 

[billchris]

It's been my experience that as soon as the client application connection is lost to SQL Server, the database automatically stops and rolls back any active transactions created by that connection.

Am I missing something?

I can think of one scenario where your problem might occur.

Let's say your application is deployed like this:

Network1             Network2
----------------     ------------------------
Client Machine   --> Application --> Database
                     Server          Server

In this scenario, a transaction started by the Application Server at the request of the Client Machine would continue to run after Network1 crashed. But, the transaction should run to completion just fine. Unless, in the middle of the transaction, the Application Server was waiting for a response from the client machine. Then what you describe would occur.

Is this anywhere close to your situation? Or, am I just being obtuse and missing something?



“I apologize for this long letter. I didn't have the time to make it any shorter” --Blaise Pascal

 

[adasoft]

Thanx for the response.But it does not solve my problem.We are usign VB as front end and SQL Server 7.0 as backend.The scenario is just like what Mr.Billchris has described.


Network1 Network2
---------------- ------------------------
Client Machine --> Database Server

If a user starts a transaction from netwrok1 and then his application terminates abnormally (before rolling back or committing), say due to power failure or if the user kills the application with Ctrl+Alt+Delete, then the transaction started by him is not terminated (rolled back) by SQL Server. Even the process cannot be killed using the "Kill" command and the user keeps holding all the locks that he has already acquired. Also nobody can use Enterprise Manager. This happens when you have lock on more than 8 tables including tables in tempdb. The only way to solve this problem is to restart the server which is never expected in a multiuser system.

One can know the threadID of the process that needs to be killed using "Sysprocesses" system table.

Is there any setting due to which SQL SERVER will automatically kill a process or rollback a transaction started by a disconnected client ?

Is it possible that a process or transaction will have to release lock after the specified time ?

Is there any other way to tackle this problem?

 

[ks2000]

SQL server automatically starts Roll back process for disconnected applications.
let us assume that you are updating one table from the network1 and your server is in Network 2, if there is any problem in the connection , then sqlserver starts roll back process for that table which u r updating through your application. and after roll back process it automatically kill that process , restarting the system is not preferrable while runnign rollback process.