locked
MSSQL Concurrancy RRS feed

  • Question

  • I have written an ActiveX control using OLE DB for an MSSQL db.  I previously used the MS provided ADODB.Recordet control to perform a SELECT within a transaction on a table with the following table hints (

    ROWLOCK, UPDLOCK, NOWAIT).  Using this statement, when the first user selected the row to update it, any other users attempting to select the row would receive a timeout error indicating that the row was already locked. Due to operational constraints I can no longer use the ADODB controls so I had to roll my own.  When using OLEDB ATL, a CCommand object executes the SELECT statement and locates the row.  I use CRowset->MoveNext() to test if the row was actually found and locked during a transaction.  The problem comes when the a second user attempts to SELECT the same record.  A call to CRecordSet->MoveNext() returns E_FAIL.  I can't tell whether the provider detected an error or a timeout occurred  What is the best way to implement concurrency checking using ole db and MSSQL? 


    Kenney


    • Edited by KennethK Sunday, September 9, 2018 2:37 AM more info
    Sunday, September 9, 2018 1:22 AM

Answers

  • Thank you for you reply.  A little background.  This is a browser app the allows the user to scan documents and store the images, along with an XML file that organizes the image into folders, on a file system.  The application in question does not actually perform any updates on the row once it has been created.   This app is called from another application so the row lock keeps two user from trying to scan documents for the same item. 

    I use the ATL class CCommand<CDynamicVariantAccessor> to run the select statement after which I check to see if the row was locked by calling MoveNext on the rowset.

    I have also discovered that when I check CDBErrorInfo .GetErrorRecords, that there is an error record that says a timeout occurred.  So this kind of solves my problem


    Kenney

    • Proposed as answer by Jack Zhang - AAA Thursday, September 20, 2018 5:38 AM
    • Marked as answer by KennethK Friday, September 21, 2018 6:58 PM
    Wednesday, September 12, 2018 1:23 PM

All replies

  • Hi,

    Thanks for post here.

    >> I have written an ActiveX control using OLE DB for an MSSQL db.  I previously used the MS provided ADODB.Recordet control to perform a SELECT within a transaction on a table with the following table hints (
    ROWLOCK, UPDLOCK, NOWAIT). 
    ...

    I apologize for the delay in responding to your question. Seems that you have used the MS provided ADODB.Recordet control to perform a SELECT within a transaction on a table with the following table hints (ROWLOCK, UPDLOCK, NOWAIT).

    UPDLOCK are associated with DML statements like Update. It is a shared lock and needs to be converted to an Exclusive lock before the original operation occurs. Also the lock occurs at row level. When a user executes an Update statement it assigns a Shared lock on all the scanned rows in the table, when it finds the row to be updated it converts it to an Exclusive lock and update operation occurs.
    HOLDLOCK are used at table level it prevents the table from being updated by any other DML transactions like an insert or an update. HOLDLOCKS are preferable to use in situations where we need only COMMITTED READS.

    Did you replace UPDLOCK with HOLDLOCK and check the result?

    And you said the problem comes when the a second user attempts to SELECT the same record. What kind of command are you using now? Have you tried using SELECT ... LOCK IN SHARE MODE? More details

    Best Wishes,

    Jack Zhang

    MSDN Community Support&amp;amp;amp;amp;lt;br/&amp;amp;amp;amp;gt; Please remember to click &amp;amp;amp;amp;amp;quot;Mark as Answer&amp;amp;amp;amp;amp;quot; the responses that resolved your issue, and to click &amp;amp;amp;amp;amp;quot;Unmark as Answer&amp;amp;amp;amp;amp;quot; if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact &amp;amp;amp;amp;lt;a href=&amp;amp;amp;amp;quot;mailto:MSDNFSF@microsoft.com&amp;amp;amp;amp;quot;&amp;amp;amp;amp;gt;MSDNFSF@microsoft.com&amp;amp;amp;amp;lt;/a&amp;amp;amp;amp;gt;

    Wednesday, September 12, 2018 3:30 AM
  • Thank you for you reply.  A little background.  This is a browser app the allows the user to scan documents and store the images, along with an XML file that organizes the image into folders, on a file system.  The application in question does not actually perform any updates on the row once it has been created.   This app is called from another application so the row lock keeps two user from trying to scan documents for the same item. 

    I use the ATL class CCommand<CDynamicVariantAccessor> to run the select statement after which I check to see if the row was locked by calling MoveNext on the rowset.

    I have also discovered that when I check CDBErrorInfo .GetErrorRecords, that there is an error record that says a timeout occurred.  So this kind of solves my problem


    Kenney

    • Proposed as answer by Jack Zhang - AAA Thursday, September 20, 2018 5:38 AM
    • Marked as answer by KennethK Friday, September 21, 2018 6:58 PM
    Wednesday, September 12, 2018 1:23 PM
  • Hi KennethK,

    I am glad you have got your solution. Please help to mark your answers.

    Your understanding and cooperation will be grateful.

    Best Regards,

    Jack Zhang



    MSDN Community Support&amp;amp;amp;amp;lt;br/&amp;amp;amp;amp;gt; Please remember to click &amp;amp;amp;amp;amp;quot;Mark as Answer&amp;amp;amp;amp;amp;quot; the responses that resolved your issue, and to click &amp;amp;amp;amp;amp;quot;Unmark as Answer&amp;amp;amp;amp;amp;quot; if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact &amp;amp;amp;amp;lt;a href=&amp;amp;amp;amp;quot;mailto:MSDNFSF@microsoft.com&amp;amp;amp;amp;quot;&amp;amp;amp;amp;gt;MSDNFSF@microsoft.com&amp;amp;amp;amp;lt;/a&amp;amp;amp;amp;gt;


    Thursday, September 13, 2018 3:11 AM