Re: Transactions in SQL Server

From: Todd (Todd_at_%RemoveThis%cprops.com)
Date: 08/06/04 

Date: Fri, 6 Aug 2004 18:00:03 -0500

Thanks Hugo. I'll try it on Monday and see if I see any difference.

Todd

"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
news:mru7h059dip0e7k4pr5aelviv8fpv76qi6@4ax.com...
> On Fri, 6 Aug 2004 15:48:40 -0500, Todd wrote:
>
> >I am having some SQL Server 2000 transactional issues and was wondering
if
> >anyone knew of some settings I need to have to prevent the following. I
have
> >a table that is called the appointments table. One of its fields is
called
> >"in_edit_by" which is used to notify the calling program that someone has
a
> >record open and is editing it. Right now, I have a the following process
> >wrapped in a transaction:
> >
> >1: Read in_edit_by into a temp variable
> >2: If in_edit_by is not null (ie, no one is editing the record) update
the
> >table and set in_edit_by = current user
> >3: Do a select of the table and tell the calling program the result can
be
> >edited.
> >(If item 2 is not editable, it will still read the data, but send a flag
> >back that the data is not editable)
> >4) Close the transaction
> >
> >Here's the actual TSQL:
> >
> >
> >BEGIN TRANSACTION trn_RetrieveAppt
> >
> >-- Mark the appointment Locked first
> >SET @LockedBy = (SELECT in_edit_by FROM appointment HOLDLOCK
> > WHERE appointment_id = @ApptID )
> >IF @LockedBy is NULL or @LockedBy = @User
> > BEGIN
> > UPDATE appointment
> > SET in_edit_by = @User,
> > ApptOpenMaintDate = getdate()
> > WHERE appointment_id = @ApptID
> > SET @isEditable = 'T'
> > END
> >ELSE
> > BEGIN
> > SET @isEditable = 'F'
> >
> > END
> >
> >--Read a bunch of stuff from this table and others and pass back to the
> >calling program including the @isEditable flag.
> >COMMIT TRANSACTION trn_RetrieveAppt
> >
> >It works for most everything, except it seems to fail when both users run
> >the query next to simultaneiously (down to less than 1 second
difference) --
> >in this case, even though wrapped in transaction, BOTH user A and B get a
> >NULL value into their temp variable which has really bad consequences
> >because the application then thinks both User A and B have editable
records.
> >It is as if, even though I specified the transaction to start at the top,
it
> >really doens't start until I begin the UPDATE. I'm wondering if this is
> >because of the SET TRANSACTION ISOLATION LEVEL default behavior? If so,
what
> >should I pick? Or can I set a ROWLOCK on the initial select? It's really
> >tough to duplicate here -- it seems to happen in the field where there's
> >lots of users accessing simulataneously.
> >
>
> Hi Todd,
>
> With the default isolation level, shared locks (used for selecting data)
> are released after executing the SELECT. This can be modified by using a
> different isolation level (repeatable read springs to mind), but that will
> only change the problem, not solve it - you'll now start running into
> deadlocks because both users have a shared lock and want to get an
> exclusive lock to do the update.
>
> You can solve this by using UPDLOCK as locking hint instead of HOLDLOCK.
> The UPDLOCK is used to signal to SQL Server that you intend to modify the
> row you are reading - SQL Server will either grant you an exclusive lock
> or let you wait until other locks on the row are released. I think you
> don't need to change the isolation level, but do test it to be sure!
>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Relevant Pages

  • Re: controlling lock order in transactions
    ... I believe the default isolation level for .net may be ... My transaction ONLY performs INSERTS. ... Yes it will put an exclusive lock on the row being inserted but Serializable ... COMMIT TRANSACTION ...
    (microsoft.public.sqlserver.programming)
  • Re: controlling lock order in transactions
    ... I checked my existing connections events in the profiler, ... I don't see anything about isolation level. ... only using transaction in stored procedures. ... I though it was always Exclusive lock. ...
    (microsoft.public.sqlserver.programming)
  • Re: Pessimistic Locking
    ... > A normal select statement executed in a transaction with serializable ... > isolation level was still possible, ... > was called which should lock the row. ... > in a transacion with serializable isolation level and another client has ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Transaction isolation levels
    ... SELECT 'X' INTO #TEMP ... locks the row in table TICKETS until the entire transaction has ... The lock taken out on TICKETS will be a shared lock ... that under the default isolation level will be released. ...
    (comp.databases.ms-sqlserver)
  • Re: Working Transactions somehow started not to work
    ... Transaction and locking are not properties of recordsets but are properties ... Opening a served-based cursor will put a lock on ... If a connection is closed, ... SL> and are not associated necessarily with server-based cursors. ...
    (microsoft.public.access.adp.sqlserver)