Need a deadly row lock through oledb

> SELECT * FROM YourTable WITH(XLOCK) WHERE YourID = @ID

Be aware. The XLOCK hint is very dangerous. There's an optimization for exclusive row locks, making
the hint useless or even dangerous in some cases. If memory serves me, the lock can be ignored by
regular reads if the row wasn't modified since the earlier open transaction. This is easy to test
and verify (that a subsequent read will read the row even though the lock is there).

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/


"Chris Lim" wrote in message

> I'm not aware of the property-setting approach you're currently using,
> but the standard way to lock a row is to use a locking hint within a
> transaction.
>
> e.g.
>
> BEGIN TRANSACTION
>
> SELECT * FROM YourTable WITH(XLOCK) WHERE YourID = @ID
>
> This keeps the row locked until you issue a COMMIT TRANSACTION.
>
> Egbert Nierop (MVP for IIS) wrote:
>> Hi,
>>
>> I might have asked this before, but since SQL 2005 was there, MS strongly
>> modified locking in favor of scalability.
>> I never got an answer that solved the problem.
>>
>> I have an app that really needs to lock a certain row to be locked so that
>> other apps in the network, get into a wait queue, until the row gets
>> released. On SQL 2000 this worked.
>>
>> Some facts,
>> 1) It is C++, no dotnet is -going- to be used ever in this app.
>> 2) The point is not scalability but functionality here.
>>
>>
>> The required properties which worked in SQL 2000 were
>>
>> propset.AddProperty(DBPROP_IRowsetChange, true);
>> propset.AddProperty(DBPROP_OTHERUPDATEDELETE, true);
>> propset.AddProperty(DBPROP_OWNUPDATEDELETE, true);
>> propset.AddProperty(DBPROP_UPDATABILITY, (LONG) DBPROPVAL_UP_CHANGE);
>> propset.AddProperty(DBPROP_SERVERCURSOR, true);
>> propset.AddProperty(DBPROP_LOCKMODE, (LONG)DBPROPVAL_LM_SINGLEROW);
>
 >> Stay informed about: Need a deadly row lock through oledb 

"Chris Lim" wrote in message

> I'm not aware of the property-setting approach you're currently using,
> but the standard way to lock a row is to use a locking hint within a
> transaction.
>
> e.g.
>
> BEGIN TRANSACTION
>
> SELECT * FROM YourTable WITH(XLOCK) WHERE YourID = @ID
>
> This keeps the row locked until you issue a COMMIT TRANSACTION.

Even without transactions, a server-side cursor, opened with a pessimistic
row lock worked.

Note it is a serverside cursor.

The code that you have shown, is common TSQL which I cannot use here.

Thanks anyway.
 >> Stay informed about: Need a deadly row lock through oledb 

Egbert Nierop (MVP for IIS) (egbert_nierop@nospam.invalid) writes:
> I might have asked this before, but since SQL 2005 was there, MS strongly
> modified locking in favor of scalability.
> I never got an answer that solved the problem.
>
> I have an app that really needs to lock a certain row to be locked so that
> other apps in the network, get into a wait queue, until the row gets
> released. On SQL 2000 this worked.
>
> Some facts,
> 1) It is C++, no dotnet is -going- to be used ever in this app.
> 2) The point is not scalability but functionality here.
>
>
> The required properties which worked in SQL 2000 were
>
> propset.AddProperty(DBPROP_IRowsetChange, true);
> propset.AddProperty(DBPROP_OTHERUPDATEDELETE, true);
> propset.AddProperty(DBPROP_OWNUPDATEDELETE, true);
> propset.AddProperty(DBPROP_UPDATABILITY, (LONG) DBPROPVAL_UP_CHANGE);
> propset.AddProperty(DBPROP_SERVERCURSOR, true);
> propset.AddProperty(DBPROP_LOCKMODE, (LONG)DBPROPVAL_LM_SINGLEROW);

It would be interesting to know what you really want to achieve. Using
SQL Server's row locks for internal application logic is a risky game.
And it does not help if you insist on not using SQL, but rely on what
the OLE DB provider produces. Now there is one more player that may
do things you don't expect it to. While it may have worked on SQL 2000,
it may be an uphill battle to get it to work on SQL 2005. The place
to start look would be to run a Profiler trace to see when the OLE DB
provider emits.

The alternatives that comes to mind is to use application locks. And,
if the purpose is to implement a queue of some sort, you should
definitely look into Service Broker.

--
Erland Sommarskog, SQL Server MVP, esquel DeleteThis @sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 >> Stay informed about: Need a deadly row lock through oledb