Re: Transactions in SQL Server

From: Wayne Snyder (wayne.nospam.snyder_at_mariner-usa.com)
Date: 08/09/04 

Date: Mon, 9 Aug 2004 08:09:18 -0400

What you are doing is called Pessimistic locking. The trouble with
pessimistic locking is that it can reduce concurrency... Make sure you do
some testing the ensure you do not get too much blocking... And make sure
there is an index on appointmentid.

Another method people use (optimistically) is to add a timestamp column to
the table.. This column value is automatically set by SQL and changed each
time there is an update to the row, and it is unique...

You may allow anyone to attempt to change the row, but during the update you
specify that the appointment ID has to match AND the timestamp has to match
the original value. If the timestamps match, no one has changed the row in
the interim.. If the timestamps do not match, then someone else has changed
the row since THIS person first started using it... 

-- 
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Todd" <Todd@%RemoveThis%cprops.com> wrote in message
news:%23Va6Sc$eEHA.904@TK2MSFTNGP09.phx.gbl...
> 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.
>
>

Relevant Pages

  • Re: Transaction Isolation Level
    ... This means that while I initiate a transaction selecting all new orders it ... My query is executed on a SQL server 2005. ... the isolation level you should use is snapshot isolation. ...
    (comp.databases.ms-sqlserver)
  • Re: Windows Completely Locks Up!! FREEZE
    ... failing to start/run the Distributed Transaction Coordinator. ... Error Specifics: ... The Business Contact Manager SQL Server Startup Service service terminated ... The service did not respond to the start or control request in a timely ...
    (microsoft.public.windowsxp.general)
  • Re: Asynchronous Stored Procedure Never Returns - Help?
    ... If you have the Sql Server 2000 or 2005 docs they are thorough and can be ... for Transaction SQL Reference from the drop-down or select a keyword from ... your query in Query Analyzer or Sql Server Management Studio, ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Transaction Abort in a trigger SQL 2008, writing to event log
    ... transaction could still be doomed at some point, ... With the current functionality in SQL Server ... you should roll back the transaction in your error handler. ... global cursor in a stored procedure. ...
    (microsoft.public.sqlserver.programming)
  • Re: Transaction Abort in a trigger SQL 2008, writing to event log
    ... I have done some stack trace type of error catching in Powerbuilder, ... transaction could still be doomed at some point, ... With the current functionality in SQL Server ... you should roll back the transaction in your error handler. ...
    (microsoft.public.sqlserver.programming)
Loading