|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
locking behaviour 2005?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! 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. -- Show quoteHope this helps. Dan Guzman SQL Server MVP "Egbert Nierop (MVP for IIS)" <egbert_nierop@nospam.invalid> wrote in message news:uK9yXJJjGHA.1260@TK2MSFTNGP05.phx.gbl... > 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 I've never configured it so.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. Sure, I mean if you close the record.> > 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. Show quote > -- > Hope this helps. > > Dan Guzman > SQL Server MVP > I ran some ADO tests with (adOpenDynamic and adLockPessimistic) and didn't
see any obvious differences in locking behavior with a simple query. However, there are many other variables involved. The provider, query particulars, table indexes and OLEDB properties can all influence cursor/locking behavior. SQL 2000 would implicitly convert some server cursor types depending on the underlying query. I don't know the details of your case but you might check out the SQL 2005 Books Online topic on cursor behavior changes (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/65eaafa1-9e06-4264-b547-cbee8013c995.htm). -- Show quoteHope this helps. Dan Guzman SQL Server MVP "Egbert Nierop (MVP for IIS)" <egbert_nierop@nospam.invalid> wrote in message news:%2314Pr0JjGHA.3816@TK2MSFTNGP02.phx.gbl... > > "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 Thanks.news:uro4DVLjGHA.4504@TK2MSFTNGP03.phx.gbl... >I ran some ADO tests with (adOpenDynamic and adLockPessimistic) and didn't >see any obvious differences in locking behavior with a simple query. >However, there are many other variables involved. The provider, query >particulars, table indexes and OLEDB properties can all influence >cursor/locking behavior. > > SQL 2000 would implicitly convert some server cursor types depending on > the underlying query. I don't know the details of your case but you might > check out the SQL 2005 Books Online topic on cursor behavior changes > (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/65eaafa1-9e06-4264-b547-cbee8013c995.htm). b.t.w. I believe that SQL 2000 really did respond correctly to the option as in adOpenDynamic and adLockPessimistic as documented, but SQL 2005, was proud to have improved scalability, because 'locking problems' should have gone according to the ads. "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message Got it.news:uro4DVLjGHA.4504@TK2MSFTNGP03.phx.gbl... >I ran some ADO tests with (adOpenDynamic and adLockPessimistic) and didn't >see any obvious differences in locking behavior with a simple query. >However, there are many other variables involved. The provider, query >particulars, table indexes and OLEDB properties can all influence >cursor/locking behavior. > > SQL 2000 would implicitly convert some server cursor types depending on > the underlying query. I don't know the details of your case but you might > check out the SQL 2005 Books Online topic on cursor behavior changes > (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/65eaafa1-9e06-4264-b547-cbee8013c995.htm). I modified the SELECT statement that used to be without a lock hint, so it contains a lock hint named 'UPDLOCK' . Now the behaviour, is the same as with SQL 2000.  |
|||||||||||||||||||||||
Other interesting topics