Database Transaction

Databases
1

I have a Utility table and a Transaction table. The Utility table contains the next transaction number.

Here is a sample of the code:

[code]
app.myDbase.SQLExecute(“Begin Transaction”)

//Look up the Utility record and get the next transaction number
//Increment the transaction number in the Utility record and save the Utility record

//Assign the current transaction the transaction number
//Save the Transaction record

If (app.myDbase.Error= False) Then
  app.myDbase.Commit
  app.myDbase.SQLExecute ("END TRANSACTION")
Else
  Dim sError As String = app.myDbase.ErrorMessage
  app.myDbase.Rollback
  MsgBox("DB Error: " + sError)
End If
[/code]

On occasion, I am getting duplicate Transaction numbers. Shouldn’t this not be possible since the entire process is enclosed in a Transaction?

2

Hard to tell as there seems to be code thats missing
If this IS all the code then no this won’t stop it from getting duplicates

You should do this in the following order (assuming your table is called NextID and the column is nextAvailable)

Begin Transaction
update NextID set nextAvailable = nextAvailable + 1
select nextAvailable from NextID
Commit
return next available

Why ?
The immediate update WILL block if there are multiple threads / process trying to do the update at once
And once they unblock they can safely read THEIR most recent update and get the right value

Doing it as read / update does not stop the read from occurring - but as update read does

3

I had a feeling that the database was allowing reading of the file during a transaction. I couldn’t imagine any other way this could happen. I thought there was some record locking going on during a transaction, but obviously not the case.

Thanks.

4

Remember that transactions make sure that all or none of your requests go through, and may not necessarily do any locking. If you don’t want anything to be able to read a table while you’re doing something else you’ll need to lock things up somehow.

5

Keeping your own next ID is a very bad idea. Let the database do it for you by using an AutoIncrement key.

6

I noticed that Norman suggested using the command “Update” within a single SQL statement. Can I assume that this command is is better than something like:

  [code]   
      vTransNumber = rs_Utility.Field("Trans_Number").Value
      vTransNumber = vTransNumber +1
      
      rs_Utility.Edit
      rs_Utility.Field("Trans_Number").Value = vTransNumber
      rs_Utility.Update
     app.myDbase.commit

[/code]

which may have a time lag allowing another user to modify the record before these commands have completed execution?

7

There is an autoincrement column as the primary key, however, these numbers represent multiple users from multiple clients (and can increment into unwieldy digits for the enduser - at least 8 digits).

Since the Utility record only has to deal with users from one client (say 10 or 20 users max), I believe crashing of users within a SQL update command would be near impossible (since the command executes within a few msec).

8

Or at least I am hoping that is so.

9

Norm, it doesn’t look like I can get a recordset from the following code:

      app.myDbase.SQLExecute("Begin Transaction")
      Dim rs_Utility as RecordSet
      sql = "UPDATE Utility" + _
      "SET Trans_Number = Trans_Number + 1 "
      app.myDbase.SQLExecute(sql)
      sql = "SELECT Trans_Number FROM Utility"
      rs_Utility = app.myDbase.SQLExecute(sql)
       vTransNumber = rs_Utility.Field("Trans_Number").Value

rs_Utility.BOF and rs_Utility.EOF are both true.

Is the update command within a transaction blocking the SQL select?

Nevermind. There was an error in the syntax above. Working fine now. Thanks.

10

Actually this is how you HAD to do it before every db supported sequences etc.
WebObjects did this for a VERY long time & still can be configured to do this for you.
Works fine as long as you do it right.

11

I was under the assumption that a SQL transaction locked all the records in the database including preventing reading the records, but I guess it locks writes but not reads.

12

Reads can proceed - but if there are updates the reads will be of the values BEFORE the transaction was started - but there are cases where this can be altered (google for dirty reads)
On some db servers transactions & what values are read can be highly configurable