Re: pbm with begin tran
From: AR (aneesh.r_at_eostek.com)
Date: 12/13/04
- Next message: Justin Drennan: "Round Up"
- Previous message: Brian Burgess: "Re: UDF in column Default Value"
- In reply to: Andrew J. Kelly: "Re: pbm with begin tran"
- Next in thread: Andrew J. Kelly: "Re: pbm with begin tran"
- Reply: Andrew J. Kelly: "Re: pbm with begin tran"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 13 Dec 2004 12:49:32 +0530
Hello Kelly,
Thanks for your reply. The sp is working fine when i run it @ the Backend.
While Running it @ the front end its again showing the Same pbm. Actually we
got a 3tier arch. the front end is in ASP.net. The component also has a
Begin .. commit tran. I commended that portion. but the same error is
showing.
regards
Aneesh
"Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message
news:uxwSJ843EHA.2600@TK2MSFTNGP09.phx.gbl...
> You need to test the value before you even attempt to update it and lock
it
> at that point so the next person can noteven select it. That way there
will
> be no issues. Also if you test for @@TRANCOUNT > 0 before issuing a
> Rollback or Commit you will not have that issue.
>
>
> CREATE PROCEDURE BookSeat
> @SeatsBooked int
> ,@ScheduleID int
> AS
> DECLARE @CurrentValue INT
>
> BEGIN TRAN
> BEGIN
>
> SELECT @CurrentValue = RemainingSeats
> FROM TrainingSchedule WITH (XLOCK)
> WHERE ScheduleID = @ScheduleID
>
> IF @CurrentValue < 1
> BEGIN
> RAISERROR('No seats available,10,1)
> IF @@TRANCOUNT > 0
> ROLLBACK TRAN
> RETURN
> END
>
> UPDATE TrainingSchedule
> SET RemainingSeats = RemainingSeats - @SeatsBooked
> WHERE ScheduleID = @ScheduleID
>
> IF @@ERROR<> 0
> BEGIN
> IF @@TRANCOUNT > 0
> ROLLBACK TRAN
> RETURN
> END
>
> END
>
> IF @@TRANCOUNT > 0
> COMMIT TRAN
>
> RETURN
>
>
> --
> Andrew J. Kelly SQL MVP
>
>
> "AR" <aneesh.r@eostek.com> wrote in message
> news:O6DXhm43EHA.1564@TK2MSFTNGP09.phx.gbl...
> > Hi
> >
> > Sorry there was only one mistake. But the code won't always give me the
> > correct answer. Sometimes it is Running perfectly. Sometimes its giving
> > the
> > error like thsi
> > "The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION."
> >
> > regards
> > AR
> >
> > "Mike Epprecht (SQL MVP)" <mike@epprecht.net> wrote in message
> > news:Od5lFi43EHA.1392@tk2msftngp13.phx.gbl...
> >> Hi
> >>
> >> Please chack that your code wortks before you post it here. First
> > debugging
> >> incorrect syntax usage and incorrect object names does make it more
> >> difficult.
> >>
> >> After fixing your code and adding the PRINTs in when rolling back, the
> > code
> >> works as expected. If you open 2 wondows, run each WAITFOR in one of
> >> them,
> >> when the time expires, one commits correctly and the other on rolls
back,
> >> with remianing seats being 0.
> >>
> >> CREATE TABLE [Schedule] (
> >> [ScheduleID] [int] IDENTITY (1, 1) NOT NULL ,
> >> [TrainingDate] [smalldatetime] NULL ,
> >> [RemainingSeats] [int] NULL ,
> >> CONSTRAINT [PK_TrainingSchedule] PRIMARY KEY NONCLUSTERED
> >> (
> >> [ScheduleID]
> >> )) ON [PRIMARY]
> >> GO
> >>
> >> INSERT Schedule values (GETDATE(), 1)
> >>
> >> GO
> >>
> >> CREATE PROCEDURE BookSeat
> >> @SeatsBooked int
> >> ,@ScheduleID int
> >> AS
> >> BEGIN TRAN
> >> BEGIN
> >> -- Here is an Insert statement for the Bookings done.
> >>
> >> UPDATE Schedule
> >> SET RemainingSeats = RemainingSeats - @SeatsBooked
> >> WHERE ScheduleID = @ScheduleID
> >>
> >> IF @@ERROR<> 0
> >> BEGIN
> >> PRINT 'Error, Rolling Back'
> >> ROLLBACK TRAN
> >> RETURN
> >> END
> >>
> >> IF (SELECT RemainingSeats FROM Schedule WHERE ScheduleID =
> >> @ScheduleID ) < 0
> >> BEGIN
> >> PRINT 'No Seats left, Rolling Back'
> >> ROLLBACK TRAN
> >> RETURN
> >> END
> >> END
> >> COMMIT TRAN
> >>
> >> GO
> >>
> >>
> >> WAITFOR time '14:48'
> >> EXEC BookSeat 1,1
> >> go
> >> WAITFOR time '14:48'
> >> EXEC BookSeat 1,1
> >> go
> >> --------------------------------
> >> Mike Epprecht, Microsoft SQL Server MVP
> >> Zurich, Switzerland
> >>
> >> IM: mike@epprecht.net
> >>
> >> MVP Program: http://www.microsoft.com/mvp
> >>
> >> Blog: http://www.msmvps.com/epprecht/
> >>
> >> "AR" <aneesh.r@eostek.com> wrote in message
> >> news:#k0iEF23EHA.404@TK2MSFTNGP10.phx.gbl...
> >> > Hi,
> >> >
> >> > Here is one problem with BEGIN TRAN AND COMMIT TRAN
> >> > My Table structure is shown Below. It's used for Scheduling the
> >> > training
> >> > Sessions for different Students.
> >> >
> >> > The problem is that Whever 2 students simultaneously register for a
> > seat,
> >> it
> >> > allows that seat to both. even if the
> >> > remaining seats = 1. My procedure will update RemainingSeats to -1
in
> >> that
> >> > case . can anyone help me to identify the Problem
> >> > Thanking in advance
> >> > AR
> >> >
> >> >
> >> >
> >> > CREATE TABLE [Schedule] (
> >> > [ScheduleID] [int] IDENTITY (1, 1) NOT NULL ,
> >> > [TrainingDate] [smalldatetime] NULL ,
> >> > [RemainingSeats] [int] NULL ,
> >> > CONSTRAINT [PK_TrainingSchedule] PRIMARY KEY NONCLUSTERED
> >> > (
> >> > [ScheduleID]
> >> > ) ON [PRIMARY]
> >> > ) ON [PRIMARY]
> >> > GO
> >> >
> >> > INSERT INTO Schedule (GETDATE(), 1)
> >> >
> >> > GO
> >> >
> >> > CREATE PROCEDURE BookSeat
> >> > @SeatsBooked int
> >> > ,@ScheduleID int
> >> > AS
> >> > BEGIN TRAN
> >> > BEGIN
> >> > -- Here is an Insert statement for the Bookings done.
> >> >
> >> > UPDATE TrainingSchedule
> >> > SET RemainingSeats = RemainingSeats - @SeatsBooked
> >> > WHERE ScheduleID = @ScheduleID
> >> >
> >> > IF @@ERROR<> 0
> >> > BEGIN
> >> > ROLLBACK TRAN
> >> > RETURN
> >> > END
> >> >
> >> > IF (SELECT RemainingSeats FROM TrainingSchedule WHERE ScheduleID =
> >> > @ScheduleID ) < 0
> >> > BEGIN
> >> > ROLLBACK TRAN
> >> > RETURN
> >> > END
> >> > END
> >> > COMMIT TRAN
> >> >
> >> > GO
> >> >
> >> > WAITFOR time ('11:30')
> >> > EXEC BookSeat 1,1
> >> >
> >> > GO
> >> > WAITFOR time ('11:30')
> >> > EXEC BookSeat 1,1
> >> >
> >> > GO
> >> >
> >> >
> >>
> >>
> >
> >
>
>
- Next message: Justin Drennan: "Round Up"
- Previous message: Brian Burgess: "Re: UDF in column Default Value"
- In reply to: Andrew J. Kelly: "Re: pbm with begin tran"
- Next in thread: Andrew J. Kelly: "Re: pbm with begin tran"
- Reply: Andrew J. Kelly: "Re: pbm with begin tran"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
