> so, it locks as soon as the record update is done.

> Hello,
>
> I know that SQL has changed a lot and that it should be backward
> compatible with 2000.
>
> (I'm certified with SQL 2000 fyi).
>
> I'm experiencing a locking problem. For some specific app, I
> really -needed- pessimistic row locking.
> That was done through OLE DB and the following settings
>
> (serverside cursor!)
> propset.AddProperty(DBPROP_IRowsetChange, true);
> propset.AddProperty(DBPROP_OTHERUPDATEDELETE, true);
> propset.AddProperty(DBPROP_OWNUPDATEDELETE, true);
> propset.AddProperty(DBPROP_UPDATABILITY, (LONG) DBPROPVAL_UP_CHANGE |
> DBPROPVAL_UP_DELETE);
> propset.AddProperty(DBPROP_SERVERCURSOR, true);
> propset.AddProperty(DBPROP_LOCKMODE, (LONG)DBPROPVAL_LM_SINGLEROW);
>
> this is more or less similar to an ADODB.Recordset being opened with
> options (adOpenDynamic, adLockPessimistic)
>
>
>
> But now, SQL 2005 simply seems to ignore these settings, and the locking
> has become optimistic, so, it locks as soon as the record update is done.
>
> Can someone shed a light on this?
>
>
>
> Thanks!
>

>
> "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message
> news:e1aEmaJjGHA.4660@TK2MSFTNGP03.phx.gbl...
>> Perhaps the database is configured for READ_COMMITTED_SNAPSHOT. This
>> will change the behavior of applications using the READ_COMMITED
>> isolation level to row-versioning instead of (pessimistic) locking.
>
> I've never configured it so.
>
>>> so, it locks as soon as the record update is done.
>>
>> Did you mean to say 'unlocks'? BTW, I've been under the impression that
>> locks were released after update, unless work is done inside an explicit
>> transaction. I could be wrong though since I never use server-side
>> dynamic cursors.
>
> Sure, I mean if you close the record.
>
>> --
>> Hope this helps.
>>
>> Dan Guzman
>> SQL Server MVP
>>
>

"Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message
news:e1aEmaJjGHA.4660@TK2MSFTNGP03.phx.gbl...
> Perhaps the database is configured for READ_COMMITTED_SNAPSHOT. This will
> change the behavior of applications using the READ_COMMITED isolation
> level to row-versioning instead of (pessimistic) locking.

>> so, it locks as soon as the record update is done.
>
> Did you mean to say 'unlocks'? BTW, I've been under the impression that
> locks were released after update, unless work is done inside an explicit
> transaction. I could be wrong though since I never use server-side
> dynamic cursors.

> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>