Transaction logging with SQL Server

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

 

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.

How do you mean you cant read from the master table? Do you get an error ?

You could allso use a trigger on the master table to insert the detail record.
In that case you get your PK from INSERTED

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.

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

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.