Solved

Transaction logging with SQL Server

Posted on 2000-05-15
8
341 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 6

Expert Comment

by:crsankar
ID: 2813160
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
ID: 2813186
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
ID: 2813381
How do you mean you cant read from the master table? Do you get an error ?
0
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 

Expert Comment

by:bucodi
ID: 2813592
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
 
LVL 5

Expert Comment

by:dtomyn
ID: 2813943
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
ID: 2814515
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
ID: 2815158
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
ID: 2817082
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL / Table Lock? 7 40
VMware PVSCSI SQL Server 2016 AlwaysOn 2 36
SQL Server Error: 4060 8 32
relocating SQL 2000 18 31
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

734 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