Solved

Transaction logging with SQL Server

Posted on 2000-05-15
8
320 Views
Last Modified: 2008-03-06
I've developed an application in A97 that performs the following sequence of tasks.


1.      begin transaction
2.      insert record into master table
3.      read record number (primary key) from master table after insert
4.      insert records in detail table using foreign key read in step 3.
5.      commit transaction



That sequence of events work fine for an Access 97 back end database.  I have since ported the back end to SQL Server 6.5 and I have found that I cannot read the record number from the master table unless I commit the transaction.  Eg. I have had to modify the program to do the following:


1.      begin transaction
2.      insert record into master table
3.      commit transaction
4.      begin transaction
5.      read record number (primary key) from master table after insert
6.      insert records in detail table using foreign key read in step 5.
7.      commit transaction


I am not very comfortable with that implementation since I am unable to rollback the insertion of the master record if I encounter an error while inserting detail records.  Any ideas on why this happens with SQL server?  Is there a work around?
0
Comment
Question by:geowilli
8 Comments
 
LVL 6

Expert Comment

by:crsankar
Comment Utility
Here is a workaround for this situation
U can set it to READ UNCOMMITTED


SET TRANSACTION ISOLATION LEVEL (T-SQL)
Controls the default transaction locking behavior for all Microsoft® SQL Server™ SELECT statements issued by a connection.

Syntax
SET TRANSACTION ISOLATION LEVEL
    {
        READ COMMITTED
        | READ UNCOMMITTED
        | REPEATABLE READ
        | SERIALIZABLE
    }

Arguments
READ COMMITTED
Specifies that shared locks are held while the data is being read to avoid dirty reads, but the data can be changed before the end of the transaction, resulting in nonrepeatable reads or phantom data. This option is the SQL Server default.
READ UNCOMMITTED
Implements dirty read, or isolation level 0 locking, which means that no shared locks are issued and no exclusive locks are honored. When this option is set, it is possible to read uncommitted or dirty data; values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction. This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. This is the least restrictive of the four isolation levels.
REPEATABLE READ
Locks are placed on all data that is used in a query, preventing other users from updating the data, but new phantom rows can be inserted into the data set by another user and are included in later reads in the current transaction. Because concurrency is lower than the default isolation level, use this option only when necessary.
SERIALIZABLE
Places a range lock on the data set, preventing other users from updating or inserting rows into the data set until the transaction is complete. This is the most restrictive of the four isolation levels. Because concurrency is lower, use this option only when necessary. This option has the same effect as setting HOLDLOCK on all tables in all SELECT statements in a transaction.
Remarks
Only one of the options can be set at a time, and it remains set for that connection until it is explicitly changed. This becomes the default behavior unless an optimization option is specified at the table level in the FROM clause of the statement.

The setting of SET TRANSACTION ISOLATION LEVEL is set at execute or run time and not at parse time.

Examples
This example sets the TRANSACTION ISOLATION LEVEL for the session. For each Transact-SQL statement that follows, SQL Server holds all of the shared locks until the end of the transaction.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

GO

BEGIN TRANSACTION

SELECT * FROM publishers

SELECT * FROM authors

....

COMMIT TRANSACTION

 

0
 
LVL 7

Expert Comment

by:spiridonov
Comment Utility
This is strange, you should be able to read your primary key inside transaction. Post your code here, please.
Also, if your   primary key has IDENTITY porperty you can get to its value by examining @@IDENTITY  variable after insert.
0
 

Expert Comment

by:deepas
Comment Utility
How do you mean you cant read from the master table? Do you get an error ?
0
 

Expert Comment

by:bucodi
Comment Utility
You could allso use a trigger on the master table to insert the detail record.
In that case you get your PK from INSERTED
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 5

Expert Comment

by:dtomyn
Comment Utility
Also, I would highly suggest that you move this sort of processing into a stored procedure.  That is, I am a believer that any time I see the words transaction and recordset that chances are this processing would be better suited on the server.  But, I may be wrong... please post your code.

By the way, I would highly suggest NOT messing with the transaction isolcation level!  It seems to me that if you need to do this that chances are you are not properly addressing the problem.
0
 
LVL 1

Author Comment

by:geowilli
Comment Utility
The following is a simplified segment of the code I am using.  Nevertheless, I still receive the same problems with this.  I have also included the table structures in this post.



/* Microsoft SQL Server - Scripting                  */
/* Server: SPIRESERVER                              */
/* Database: AirManager                              */
/* Creation Date 16/05/2000 9:59:14 AM                   */

/****** Object:  Table dbo.Reservation    Script Date: 16/05/2000 9:59:14 AM ******/
if exists (select * from sysobjects where id = object_id('dbo.Reservation') and sysstat & 0xf = 3)
      drop table dbo.Reservation
GO

/****** Object:  Table dbo.ReservationPassenger    Script Date: 16/05/2000 9:59:14 AM ******/
if exists (select * from sysobjects where id = object_id('dbo.ReservationPassenger') and sysstat & 0xf = 3)
      drop table dbo.ReservationPassenger
GO

/****** Object:  Table dbo.Reservation    Script Date: 16/05/2000 9:59:15 AM ******/
CREATE TABLE dbo.Reservation (
      ReservationNumber int IDENTITY (1, 1) NOT NULL ,
      ReservationDate datetime NULL ,
      ReservationStatus varchar (5) NULL ,
      AgencyId varchar (5) NULL ,
      TicketAgent varchar (40) NULL ,
      NumberOfPassengers real NULL ,
      ReservationNotes varchar (150) NULL ,
      ContactName varchar (40) NULL ,
      ContactNumber varchar (13) NULL ,
      ContactEmail varchar (40) NULL ,
      PersonalRecordLocator varchar (10) NULL ,
      ReservationRequestNumber int NULL
)
GO

setuser 'dbo'
GO

EXEC sp_bindefault 'dbo.UW_ZeroDefault', 'Reservation.NumberOfPassengers'
GO

EXEC sp_bindefault 'dbo.UW_ZeroDefault', 'Reservation.ReservationRequestNumber'
GO

setuser
GO

/****** Object:  Table dbo.ReservationPassenger    Script Date: 16/05/2000 9:59:15 AM ******/
CREATE TABLE dbo.ReservationPassenger (
      ReservationNumber int NULL ,
      FirstName varchar (20) NULL ,
      MiddleName varchar (20) NULL ,
      LastName varchar (30) NULL ,
      PassengerNumber real NULL ,
      TravelAddress varchar (50) NULL ,
      TravelTelephoneNumber varchar (13) NULL ,
      TicketNumber varchar (13) NULL ,
      ReservationStatus varchar (3) NULL ,
      CancellationNumber int NULL ,
      CancellationDate datetime NULL ,
      ReservationPassengerNumber int IDENTITY (1, 1) NOT NULL
)
GO

setuser 'dbo'
GO

EXEC sp_bindefault 'dbo.UW_ZeroDefault', 'ReservationPassenger.CancellationNumber'
GO

EXEC sp_bindefault 'dbo.UW_ZeroDefault', 'ReservationPassenger.PassengerNumber'
GO

EXEC sp_bindefault 'dbo.UW_ZeroDefault', 'ReservationPassenger.ReservationNumber'
GO

setuser
GO





' begin module
Option Compare Database
Option Explicit

Private Sub main()
    Dim dbCreateReservation As Database
    Dim lngReservationNumber As Long
    Dim intPassengerCount As Single
    Dim rsReservationNumber As Recordset
    Dim strInsertPassengers As String
    Dim strInsertReservation As String
    Dim strLastName As String
    Dim strReservationDate As String
    Dim strSelectReservationNumber As String

    Dim wsp As Workspace
   
    strReservationDate = Format$(DATE, "MMMM dd, yyyy")
   
    ' create SQL statement to create the basic reservation record
    strInsertReservation = "insert into Reservation(ReservationDate, ReservationStatus)" & _
         " values ('" & strReservationDate & "', 'HK')"

    'set up work space for implementing transaction logging
    Set wsp = DBEngine.Workspaces(0)

    Set dbCreateReservation = CurrentDb
   
    On Error Resume Next
   
   ' begin transaction logging
    wsp.BeginTrans
   
    ' execute the statement to insert the reservation record
    dbCreateReservation.Execute (strInsertReservation)

    ' display error message and rollback transaction log if error is encountered
    If Err.NUMBER Then
        MsgBox "Error Inserting Reservation Record", vbCritical, "Create Reservation"
        MsgBox Err.DESCRIPTION, vbCritical, "Create Reservation"
           
        Exit Sub
    End If
 
 ' --- > This is the point at which I need to commit the previous transaction in order
 ' --- > for the following select statment to work.
 
    ' prepare SQL statement to retrieve the reservation number created for the
    ' reservation record.
    strSelectReservationNumber = "select max(ReservationNumber) as MaxReservationNumber from Reservation"

    ' open recordset to get the reservation number
    Set rsReservationNumber = dbCreateReservation.OpenRecordset(strSelectReservationNumber, dbOpenForwardOnly, dbSeeChanges, dbReadOnly)

    If Err.NUMBER Then
        MsgBox "Error Selecting Reservation Number", vbCritical, "Create Reservation"
        MsgBox Err.DESCRIPTION, vbCritical, "Create Reservation"
       
        Exit Sub
    End If
   
    ' assign reservation number
    lngReservationNumber = rsReservationNumber("MaxReservationNumber")
   

    ' The following code segment inserts all the passenger records in the ReservationPassenger
    ' table
    For intPassengerCount = 1 To 5
        strLastName = "Passenger " & intPassengerCount
       
        strInsertPassengers = "insert into ReservationPassenger(ReservationNumber, LastName, PassengerNumber, ReservationStatus)" & _
            " values (" & lngReservationNumber & _
            ", '" & strLastName & "'" & _
            ", " & intPassengerCount & ", 'HK')"

        dbCreateReservation.Execute (strInsertPassengers)
       
        If Err.NUMBER Then
            MsgBox "Error Creating Passenger Record", vbCritical, "Create Reservation"
           
            MsgBox Err.DESCRIPTION, vbCritical, "Create Reservation"
   
            wsp.Rollback
           
            Exit Sub
        End If
   
    Next intPassengerCount
   
    wsp.CommitTrans
    If Err.NUMBER Then
        MsgBox "Error Creating Passenger Record", vbCritical, "Create Reservation"
           
        MsgBox Err.DESCRIPTION, vbCritical, "Create Reservation"
    End If
   
    ' close the workspace
    wsp.Close
   
    ' set workspace to nothing
    Set wsp = Nothing
   
    ' close recordsets
    rsReservationNumber.Close
   
    ' kill recordsets
    Set rsReservationNumber = Nothing
   
End Sub
0
 
LVL 5

Accepted Solution

by:
dtomyn earned 50 total points
Comment Utility
First of all, you might want to look into changing the datatype of "PassengerNumber" to something other than "real"... perhaps Decimal or INT?

Second, for a quick solution, use a recordset to insert the data and use the bookmark property.  For example,
....
    Set wsp = DBEngine.Workspaces(0)

    Set dbCreateReservation = CurrentDb
    Set rst = dbCreateReservation.OpenRecordset("SELECT * FROM Reservation WHERE 1 = 2", dbOpenDynaset, dbSeeChanges)
     
    On Error Resume Next
     
   ' begin transaction logging
    wsp.BeginTrans
     
    With rst
        .AddNew
            ![ReservationDate] = strReservationDate
            ![ReservationStatus] = "HK"
        .Update
        .Bookmark = .LastModified
        lngReservationNumber = ![ReservationNumber]
    End With
....

Third, a better solution would be to use DAO w/ODBC Direct and calling a stored procedure.  A Quick example would be (I just threw this together):
1-Create a new CLASS MODULE called clsServerConnection

Option Compare Database
Option Explicit

Private mstrServer As String
Private mstrDatabase As String
Private mcnConnection As DAO.Connection
Private mwsWorkspace As DAO.Workspace
Private mstrUID As String
Private mstrPWD As String

Public Property Let ServerName(ByVal vstrNewValue As String)
    mstrServer = vstrNewValue
End Property

Public Property Let DatabaseName(ByVal vstrNewValue As String)
    mstrDatabase = vstrNewValue
End Property

Public Property Let UID(ByVal vstrNewValue As String)
    mstrUID = vstrNewValue
End Property

Public Property Let PWD(ByVal vstrNewValue As String)
    mstrPWD = vstrNewValue
End Property

Private Sub SetWorkspace()
    If mwsWorkspace Is Nothing Then
        Set mwsWorkspace = CreateWorkspace("NewODBCDirect", "admin", _
                "", dbUseODBC)
        Workspaces.Append mwsWorkspace
    End If
End Sub

Public Sub Connect()
    Dim strConnect As String
'    strConnect = "ODBC;Driver=SQL Server" & _
                ";Server=" & mstrServer & _
                ";Database=" & mstrDatabase & _
                ";Trusted_Connection=TRUE"
    Call SetWorkspace
    strConnect = "ODBC;Driver=SQL Server" & _
                ";Server=" & mstrServer & _
                ";Database=" & mstrDatabase & _
                ";uid=" & mstrUID & ";pwd=" & mstrPWD
    Set mcnConnection = mwsWorkspace.OpenConnection("", dbDriverComplete, _
        False, strConnect)
End Sub

Public Property Get SQLConnection() As DAO.Connection
    Set SQLConnection = mcnConnection
End Property

2-Create a new STORED PROCEDURE on your Server
CREATE PROCEDURE usp_TryMe
@ReservationDate as datetime,
@ReservationStatus AS CHAR(3)
AS
DECLARE @ReservationNumber AS INT
DECLARE @LastName AS VARCHAR(30)
DECLARE @PassengerCount AS INT

INSERT INTO Reservation
      (ReservationDate, ReservationStatus)
VALUES (@ReservationDate, @ReservationStatus)

SELECT @ReservationNumber = @@IDENTITY

SELECT @PassengerCount = 1
WHILE (@PassengerCount < 5)
    BEGIN
        SET @LastName = 'Passenger ' + CAST(@PassengerCount AS VARCHAR(10))
        INSERT INTO ReservationPassenger
            (ReservationNumber, LastName, PassengerNumber, ReservationStatus)
        VALUES
            (@ReservationNumber, @LastName, @PassengerCount, 'HK')
        SELECT @PassengerCount = @PassengerCount + 1
    END

3-Call the stored procedure using ODBCDirect
Option Compare Database
Option Explicit

Sub main()
    Dim x As clsServerConnection
    Dim y As DAO.Connection
    Dim strSQL As String
    Dim qd As DAO.QueryDef
   
    Set x = New clsServerConnection
   
    x.DatabaseName = "DarekTestDB"
    x.ServerName = "LR414W81"
    x.UID = "sa"
    x.PWD = ""
    x.Connect
   
    Set y = x.SQLConnection
    strSQL = "{ Call usp_TryMe (?, ?) }"
    Set qd = y.CreateQueryDef("", strSQL)
    qd.Parameters(0).Value = Date
    qd.Parameters(1).Value = "ABC"
   
    qd.Execute
    Set x = Nothing
End Sub


....If you have any other questions and want to contact me directly, e-mail me directly at dtomyn@tmctech.com
0
 
LVL 1

Author Comment

by:geowilli
Comment Utility
I'll probably go ahead and implement some functions in stored procedures as you indicated since it should decrease my network traffic and give me performance increases. I will need to make some modifications to the code provided to implement the transaction logging the way I need to but I think it is of great help.  
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now