"Zombie transaction"?

I'm not sure if this is the right thread but here goes. If I call on a SPROC from VB like so:

Code:

---

                        dbCmd = New SqlCommand("spNewPatient", dbConn, dbTran)
                        dbCmd.CommandType = CommandType.StoredProcedure
                        For Each p As SqlParameter In _params
                            dbCmd.Parameters.Add(p)
                        Next
                        objPatientID = dbCmd.ExecuteScalar()
                        If objPatientID IsNot Nothing Then
                            intPatientID = CInt(objPatientID)
                        End If
                        dbTran.Commit()

                    Catch ex As Exception
                        MsgBox(ex.Message & strHelpDirections, MsgBoxStyle.Exclamation, errParsing)
                        Try
                            If Not dbTran.Connection Is Nothing Then
                                dbTran.Rollback("trnAddProfile")
                                dbTran.Dispose()
                            End If
                        Catch ex2 As SqlException
                            MsgBox("Failed to rollback changes. Please contact your system administrator." & vbCrLf & vbCrLf &
                              "Exception Type: " & ex2.GetType().ToString(), MsgBoxStyle.Exclamation, "Rollback Failed")
                        End Try

---

Do I need to have a Try-Catch block (for rollbacks) in my SPROC? I'm trying the code out, and deliberately making it fail, but it seems like the code still pulls through and doesn't rollback changes (evidenced by the increase in an Identity field).

Code:

---

        BEGIN TRY
                BEGIN TRANSACTION AddPatient
                        INSERT INTO tblPatient (
                        ... );
                        SELECT SCOPE_IDENTITY() AS PatientID;

                COMMIT TRANSACTION
        END TRY

        BEGIN CATCH
                ROLLBACK TRANSACTION AddPatient;
        END CATCH
END

---

In addition, I'm getting a foreign key error if I use the code below instead of using the SQL code above:

Code:

---

...
--SELECT SCOPE_IDENTITY() AS PatientID;

COMMIT TRANSACTION
RETURN SCOPE_IDENTITY();

---

Which is how I can see that there was an increase in the Identity field, however, the new entry was not created which leads me to believe that the transaction did rollback properly.

Databases avoid re-use of identity values, so that the data is protected - if they were re-used then you could accidentally work with the wrong record more easily (eg: you create a new record and print it out, then rollback, but somebody uses the printed copy to update the record that doesn't exist any more - instead they update the replacement record).

The numbers will get re-used eventually (if all other available numbers are used), but after a much longer time, so the chances of problems caused by re-use is dramatically reduced.


Does any data get added to the table? Based on what you've said I suspect not, in which case I would recommend ignoring it.

While you can force the database system to re-use the numbers as soon as possible, the default behaviour is actually a good thing (and doesn't cause problems, except gaps in the numbers which can occasionally cause questions/confusion/etc like you have now).

Personally I'd either use DB transaction or code transactions... but not both. Pick one or the other as your standard and stick with it.

ExecuteScalar returns the first value of the first row... which comes from a select. If you use RETURN to return a value, you have to add an additional parameter with a .Direction of ReturnValue to get to the value.

-tg