Locking

Our database hase been designed around the optimistic locking strategy - this has worked well for many years- however we have now ported our database to ms sql server - where the default locking strategy is pessimistic - This is fine for most transactions - but I badly need to change the default to optimistic for one particular area of the system - how do I do this at the server side
In short - How do I change the default locking strategy from pessimistic to optimistic
We are using Borland delphi and bde to communicate with the database.Actually the default is Optimistic as far as SQL Server itself is concerned.
You should investigate how your connections are being made to the server and
what they are setting. For instance you may be set to use the SERIALIZABLE
transaction isolation mode and set as with IMPLICIT TRANSACTIONS. I would
check out SET IMPLICIT_TRANSACTIONS and SET TRANSACTION ISOLATION LEVEL in
BooksOnLine. You can useProfiler to see what your front end is sending to
SQL Server when you connect.
--
Andrew J. Kelly SQL MVP
"scimitar" <julian@.phoenix7.co.uk> wrote in message
news:AD2AA861-40AE-47DE-852A-4D4264781418@.microsoft.com...
> Our database hase been designed around the optimistic locking strategy -
this has worked well for many years- however we have now ported our database
to ms sql server - where the default locking strategy is pessimistic - This
is fine for most transactions - but I badly need to change the default to
optimistic for one particular area of the system - how do I do this at the
server side.
> In short - How do I change the default locking strategy from pessimistic
to optimistic ?
> We are using Borland delphi and bde to communicate with the database.|||Andrew, Pessimistic is the default.
The only way to obtain optimistic concurrency control is from an application
using some sort of GUID or timestamp or you would have to use some
combination of nolock,update locks ,etc . If you provide some more info, Im
pretty sure someone could assist you
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:edbBIXc%23DHA.488@.TK2MSFTNGP12.phx.gbl...
> Actually the default is Optimistic as far as SQL Server itself is
concerned.
> You should investigate how your connections are being made to the server
and
> what they are setting. For instance you may be set to use the
SERIALIZABLE
> transaction isolation mode and set as with IMPLICIT TRANSACTIONS. I
would
> check out SET IMPLICIT_TRANSACTIONS and SET TRANSACTION ISOLATION LEVEL in
> BooksOnLine. You can useProfiler to see what your front end is sending to
> SQL Server when you connect.
> --
> Andrew J. Kelly SQL MVP
>
> "scimitar" <julian@.phoenix7.co.uk> wrote in message
> news:AD2AA861-40AE-47DE-852A-4D4264781418@.microsoft.com...
> > Our database hase been designed around the optimistic locking strategy -
> this has worked well for many years- however we have now ported our
database
> to ms sql server - where the default locking strategy is pessimistic -
This
> is fine for most transactions - but I badly need to change the default to
> optimistic for one particular area of the system - how do I do this at the
> server side.
> >
> > In short - How do I change the default locking strategy from pessimistic
> to optimistic ?
> >
> > We are using Borland delphi and bde to communicate with the database.
>|||> The only way to obtain optimistic concurrency control is from an
application
> using some sort of GUID or timestamp
Although a rowversion column makes the task easier, even without one you can
compare column values with those initially retrieved. For example:
UPDATE MyTable
SET MyData = @.NewValue
WHERE MyPK = @.MyPK AND
MyData = @.OldValue
IF @.@.ROWCOUNT = 0
RAISERROR('Data modified by another user', 16, 1)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uDp3$We%23DHA.1804@.TK2MSFTNGP12.phx.gbl...
> Andrew, Pessimistic is the default.
> The only way to obtain optimistic concurrency control is from an
application
> using some sort of GUID or timestamp or you would have to use some
> combination of nolock,update locks ,etc . If you provide some more info,
Im
> pretty sure someone could assist you
> "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> news:edbBIXc%23DHA.488@.TK2MSFTNGP12.phx.gbl...
> > Actually the default is Optimistic as far as SQL Server itself is
> concerned.
> > You should investigate how your connections are being made to the server
> and
> > what they are setting. For instance you may be set to use the
> SERIALIZABLE
> > transaction isolation mode and set as with IMPLICIT TRANSACTIONS. I
> would
> > check out SET IMPLICIT_TRANSACTIONS and SET TRANSACTION ISOLATION LEVEL
in
> > BooksOnLine. You can useProfiler to see what your front end is sending
to
> > SQL Server when you connect.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "scimitar" <julian@.phoenix7.co.uk> wrote in message
> > news:AD2AA861-40AE-47DE-852A-4D4264781418@.microsoft.com...
> > > Our database hase been designed around the optimistic locking
strategy -
> > this has worked well for many years- however we have now ported our
> database
> > to ms sql server - where the default locking strategy is pessimistic -
> This
> > is fine for most transactions - but I badly need to change the default
to
> > optimistic for one particular area of the system - how do I do this at
the
> > server side.
> > >
> > > In short - How do I change the default locking strategy from
pessimistic
> > to optimistic ?
> > >
> > > We are using Borland delphi and bde to communicate with the database.
> >
> >
>|||Were you using previously Interbase?
"scimitar" <julian@.phoenix7.co.uk> wrote in message
news:AD2AA861-40AE-47DE-852A-4D4264781418@.microsoft.com...
> Our database hase been designed around the optimistic locking strategy -
this has worked well for many years- however we have now ported our database
to ms sql server - where the default locking strategy is pessimistic - This
is fine for most transactions - but I badly need to change the default to
optimistic for one particular area of the system - how do I do this at the
server side.
> In short - How do I change the default locking strategy from pessimistic
to optimistic ?
> We are using Borland delphi and bde to communicate with the database.