Re: Locking

From: Hassan (fatima_ja_at_hotmail.com)
Date: 02/23/04 

Date: Mon, 23 Feb 2004 00:19:24 -0800

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.
>
>

Relevant Pages

  • Re: High availability
    ... Thanks Geoff and Andrew for the good info, I guess my best bet is to scale up ... setting up sql server failover clustering, replication, High availability, ... Senior Database Administrator ...
    (microsoft.public.sqlserver.clustering)
  • Re: High availability
    ... I agree with Andrew. ... Senior Database Administrator ... Microsoft SQL Server MVP ... Both scaling up and>>> out ...
    (microsoft.public.sqlserver.clustering)
  • Re: How to convert the SQL Developer Edition to SQL Standard Editi
    ... drivers point since the server will be the same as long as you upgrade the ... Andrew J. Kelly SQL MVP ... >>> under Database properties->Data Files, ... >>>> you can not avoid rerunning setup for SQL Server to get to Std ...
    (microsoft.public.sqlserver.setup)
  • Migration Question Again...
    ... Sorry to ask a stupid question again since this is the first time that i do migration. ... I've got response from Andrew. ... for SQL Server ... I am not sure if I should use Copy Database Method to move all the objects or use sp_attached/sp_detached. ...
    (microsoft.public.sqlserver.setup)
  • Re: Locking
    ... Actually the default is Optimistic as far as SQL Server itself is concerned. ... transaction isolation mode and set as with IMPLICIT TRANSACTIONS. ... 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 ...
    (microsoft.public.sqlserver.server)