Concurrency problem

We got an old system here in .NET 4.0 using an Oracle DB. At some point, some calculus are made and the system "locks" that register in the table using the following command: "SELECT [the registry that will be update by the calculus] FOR UPDATE NOWAIT". So if anyone tries to calculate or update the registry with that ID it won't work. After calculating, the system connects to the DB again and then "releases" the registry by simply executing a Rollback command.

Theoretically, this is correct for the system(altought I don't like it).

Anyway, here is the thing: if something is being calculated(and these calculous take like 5 minutes to finish) and in the middle of the calculous I close the browser, the registry will still be "locked", only being unlocked when IIS restarts(actually I dunno if it's released first, I forced IIS restart 'cause it was taking too long).

Could anybody give me a light about how to deal with that? Or if you guys have a better approach to deal with this tipe of concurrency, I'll be greatful.

Thanks! Gabriel.

Adding some code. Before starting the calculus, this method is called:

Public Shared Sub LockAndUnlock(ByVal pTable As String, ByVal pWhere As String, ByVal pConnectionString As String, ByVal pLock As Boolean)

    Dim vConnection As OleDbConnection = New OleDbConnection(pConnectionString)
    Dim vCommand开发者_开发百科 As OleDbCommand = New OleDbCommand

    Try
        vCommand.Connection = vConnection
        If (pLock) Then
            vCommand.CommandText = "SELECT * " &
                                   "  FROM   " & pTable &
                                   " WHERE   " & pWhere &
                                   " FOR UPDATE NOWAIT"

            vConnection.Open()
            vCommand.ExecuteNonQuery()
        Else
            vCommand.CommandText = "ROLLBACK"

            If (vConnection.State = ConnectionState.Closed) Then
                vConnection.Open()
            End If

            vCommand.ExecuteNonQuery()
            vConnection.Close()
        End If

    Catch ex As Exception
        Throw ex
    End Try
End Sub

If the parameter pLock is True then the method locks. Otherwise, it unlocks.

---

I'd have to see some code, but I'm curious as to what you mean when you say "the system connects to the db again" and then releases the lock.

The fact that the lock is released when IIS restarts indicates to me that the connection is still active. Are you using the oracle provider, or the MS Oracle provider?

The only time I've had to deal with a long-running process I used a scheduler to run it in off-hours - a 5 minute transaction is really not suited to a web-based application.

---

It doesn't depend upon the browser because the transaction is executing in the Oracle server which is connected to by the (ASP?).NET client. Killing IIS will unlock it because it severs the connection between the DB server and the DB client which the transaction is running on.

Now, here-in lies sort of the crux of the problem :-) One could use polling from the web-client and have the server detect when not enough polls have been received. However, this has the big problem that there is (generally) no way to make sure the web-service worker that started the transaction is resumed (I think this can be done with some continuation setups, but they aren't common [yet].)

So, here I propose two methods:

1) Have an additional broker, such as a system service that can be given commands from the web-server, in addition to some kind of web-client poll. This would start any transactions, assign ids, automatically abort if polls stopped coming in, etc. This would make it possible to always "connect back" to the correct transaction to abort or pickup results or whatnot. Yuck.

2) Since the web-service worker is "busy" and can't be used by other connections until the transaction completes anyway... just have the web-server keep the web-client connection alive until the DB transaction completes (e.g. the request would be opened in a new browser window that would read: "Closing this will abort request <spinning thing>"). Of course, this depends on 1) the ability for the particular server to stream content 2) the ability (implicit/explicit) of making sure the transaction is closed when the request completes normally or when the stream is disconnected -- I have never tried in (ASP?).NET. Still yuck.

Edit: The second example could be done without streaming content -- e.g. hidden behind an AJAX call or IFRAME on a "let the user know the transaction is going on page". The key thing is that it needs to be setup so when web transaction ends, it will terminate the database transaction near-immediately. This will depend upon web-server.

For ASP.NET, see Response Stream as a way, with threading, to stream and/or detect a broken HTTP response connection.

---

I'd use an Oracle Profile with a maximum IDLE_TIME of about 10 minutes.

---

Most modern dbs has developed a very good way of providing data consistency, its part of what you're paying for actually, so take advantage. By this I'm not sure why the select for update is needed here.

The default isolation level for Oracle is read committed, in which dirty reads are not possible. You will be guaranteed to get consistent results without reads blocking writes and writes blocking reads (using SCNs and rollback and more fun stuff, you can get more into this through the Oracle concepts pdf found here).

Anyway, for most cases, if you want to do an update, do it (without worrying about putting locks around everything). Other users will still get a read consistent view of the data as it exists at the time of the read (even if an uncommitted update is ongoing).

One thing to add: The "for update" will release internal locks upon commit or rollback. The user defined/maintained "pLock" logic is a bit scary (can be buggy).