Re: OLE DB Cursors

Hi Linz,

You don't do this with properties in the SQL Server OLE DB provider. In
order to lock rows while you're updating (NOT a good idea for performance,
be warned), you need to begin a transaction at the Repeatable Read isolation
level or above (repeat, NOT a good idea). There are some properties that
refer to whether or not you can "see" updates made inside a rowset, but
that's orthorgonal to locking. In addition, there is are a few "misleading"
properties on the rowset that talk about concurrency, but none of these
implement the pessimistic concurrency you're talking about.

If the property DBPROP_CLIENT_CURSOR (I believe) is set to true, you have a
client-side cursor, otherwise you have a server-side cursor managed from the
client.

Just for my own curiousity, why do you feel that you need to manipulate rows
from the client/middle tier while keeping them locked on the server?? This
is the root cause of many a SQL locking problem (unless you're using a
versioning database (SQL2000 is not one), its not as big of a problem
there). Why not use SQL UPDATE statements?

Cheers,
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb


"Linz" <Linz@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:04FA78A4-E1B0-42CE-A93B-D571C0AC099C@xxxxxxxxxxxxxxxx
> Hello All,
>
> I'm doing some SQL Server 2000 operations using OLEDB consumer template
> classes. I have to do a lot of updates() here, its working fine but I
> think
> its optimistic lock / client cursor. My requirement is when I open the
> rowset/cursor for updates, it should lock (it shouldn't allow others to
> edit
> this row) .. I think, I have to set pessimistic lock for this, how can I
> do
> this in OLE DB .. Please help me ..
>
> TIA ..
>
> Linz
>
>


.

Relevant Pages

  • Re: Data/index locking in SQL server
    ... The lock on the table should only be a shared or intent lock which will not ... block other updates unless they are attempting to get exclusive table locks. ... This is actually a method to reduce deadlocks not cause them. ... > I found that SQL Server, ...
    (microsoft.public.sqlserver.programming)
  • Re: Cannot update
    ... not install the updates you referred to. ... highly recommend way for the later one is to install SQL server, ... Microsoft CSS Online Newsgroup Support ...
    (microsoft.public.windows.server.sbs)
  • Re: Problem with FileLock
    ... To synchromize the updates I am using FileLock. ... Everything works as advertised (ie if a machine gets the lock, ... writes to the file while the other machines wait, ... process that does so on behalf of the other (client) processes, ...
    (comp.lang.java.programmer)
  • Irregular read-only errors
    ... not allowing me to open a recordset with the correct cursor and lock ... I'm about ready to switch to using SQL Server (which I know I probably ... be an easy switch so if I could sort out Access for the time being ... Would setting the ADO connection mode to adModeReadWrite accomplish ...
    (microsoft.public.inetserver.asp.db)
  • Re: Locking question when using Select clause with For Update and Skip locked
    ... This is working fine in SQL Server and multiple session can get the ... But in Oracle the first session only return 1 row but locks all the ... It looks like both the session got the ROW-X lock but one session is ...
    (comp.databases.oracle.server)
Loading