Post reply

Rowlevel Locking in Access Project with Sql Server

  • May 27, 2004 at 2:17 pm

    #65189

    I'm trying to verify that when I'm setting the record or row source for items on a form in access, that they underlying record or row source is being locked at the record level.

    I read one disturbing comment in the access help file that said record sources from ODBC connections automatically have 'no lock' locking in place - which means deadlocks are occurring. That certainly would explain the performance issues I'm running into.

    How do I find out what kind of locking is being used, and how do I change it to row level locking if that is not the current lock being used?

     

    Thanks very much for your time!

     

    Rachel

  • May 28, 2004 at 6:14 am

    #508172

    Rachel,

     

    Why are you not using the Access Data Project (ADP)? 

    Access works with page locking and not record locking wehn using native Jet databases and, as far as I know, ODBC databases.  Since the ADP is ADO, I believe that it can implement the row level locking that you need.

    Robert

  • May 28, 2004 at 9:56 am

    #508240

    I *am* using an ADP. Thanks!

  • May 28, 2004 at 10:19 am

    #508246

    It is set as an undatable snapshot.  Are your users trying to update the same record and causing the deadlock?

  • May 28, 2004 at 3:09 pm

    #508298

    They shouldn't have been, but of course I can't guarantee that they weren't...

     

    Thanks!

  • May 28, 2004 at 9:13 pm

    #508314

    There are two ways you can get information about locks:

    Transact SQL command sp-lock or

    SQL Server Enterprise Manager in the “Current Activity Window”

    If you need to control the type of locks acquired on a table use table-level locking hints. Locking hints can be specified using the SELECT, INSERT, UPDATE, and DELETE statements. These hints override the type of locks which SQL Server determines automatically.

     

  • May 28, 2004 at 11:35 pm

    #508316

    I was reading about that - but I got the impression that was something that had to be done inside sql server. I need to check how the locking is going from Access as it interfaces with sql server and I'm not clear on how to do that.

    I will start checking on this to see what I can see.

    Thanks!

  • May 29, 2004 at 7:43 am

    #508320

    Rachel,

    SQL Server has an automatic locking mechanism that allows different types of resources to be locked by a transaction. SQL Server automatic locking mechanism selects appropriate locks for resources (single row, row lock within and index, page, contiguous group of eight pages, or index pages, entire table, database) automatically within each transaction. This automatic locking is performed for all transactions regardless of the client software (MS ACCESS, EXCEL, VB, VBA, etc) which initiate the transaction.

    SQL Server locks resources using different lock modes (shared, update, exclusive, intent, schema, bulk update) that determine how the resources can be accessed by concurrent transactions.

    Review “Locks Object “in BOL.

    If your application is not giving you a specific problem, I would recommend let SQL Server perform the automatic locking rather than you providing a hint.

    Please, describe the problem if you have one.

    Regards.

     

  • May 30, 2004 at 3:46 am

    #508336

    Please read this carefully:

    When you want a report which based on complicated queries joined from several tables, These tables are updated frequently, then you lock these tables (or you will wait for previous transaction lock on these tables to be completed to put your locks on them) .. so all other poeple using these tables are locked too (so hang happened - because they are in the queue of lock of these tables)!!

    if you use a query in Access and make a report based on it, and in that query "properties" you choose "No locks" in "Record locks", the query will lock some tables or pages (so although you choose "No locks", it still makes locks!! -the lock type is IS lock- .. because it is a bug in Access with SQL Server)

    the best thing is to run the query on the server (by making a view on SQL SERVER) .. and with each table name in the query, add this statement "with (nolock)" .. so you read data as it is (not recently updated = not commited transaction), and you don't wait for locks to be free and you don't lock these tables, and any other transaction will complete successfully (for you and others) .

    you will write something like this:

    select Invoices.*, Situation.*

    from Situation with (nolock) INNER JOIN Invoices with (nolock)

         ON Situation.SituationID = Invoices.Situation

    where Situation.SituationID =1

    Disadv:

    - when using "with (nolock)", you will not seen comitted transaction (changed one) at this moment ... but you can see it at any other time.

    Adv:

    - no hang at all

    - very fast Response

    - Little summary for Locks in SQL Log file.

    also : you may need to add more Memory (RAM) when server still hangs a little after transfering  your queries to Server.... becuase every transaction or query is done on server first, then processed data is passed to your PC.

    I hope this will help you to solve the problem


    Alamir Mohamed
    Alamir_mohamed@yahoo.com

  • May 30, 2004 at 8:36 am

    #508337

    Alamir,

    Actually, the best if you are going to use an Access query is to use a snapshot.  Then there are no record locks of any kind that are active.

  • May 31, 2004 at 12:55 am

    #508351

    also if you make snapshot .. there is LOCK !! ..

    try to make any regular query .. and adjsut it to snapshot .. and on SQL Query analayzar run SP_Lock .. and see what will happened..


    Alamir Mohamed
    Alamir_mohamed@yahoo.com

  • March 24, 2008 at 10:11 pm

    #793320

    Hi,

    I have a question relating to this thread so I thought I'd did it up after so many years.

    I am using Microsoft Access as a front end with SQL Server Expreass 2005. I have come across the bug in Access with SQL Server where, if I set as 'No Lock' in Access it still asks the user whether to 'Drop Changes' (if another user has changed the data since this user retrieved it).

    The locking that is occuring is acceptable because it will force the user to re-enter the data. Although, I would like intercept this error message and write my own, while at the same time, forcing the 'Drop Changes' option to occur. I would like this to occur when working with both Queries and Forms. Does anyone have any suggestions on how to do this.

    Thanks

  • March 25, 2008 at 8:02 am

    #793506

    If you are going to use it with Accss, you need to add a timestamp column on each table. The problem you are finding is not a bug. More than likely, it is a floating point issue. Access cannot tell if the record has been changed because the floating point is changing due to the ODBC driver used to connect to the data. Using the timestamp column will allow it to be able to understand that the record has not been changed (unless of course, it has been).

  • March 25, 2008 at 5:48 pm

    #793799

    Hi,

    Thanks for your reply. This only happens when data has been changed on the same record, so I don't think it is a timestamp/floating point issue (although, thanks for the tip).

    I am happy that it does come up with the message, but would like to insert a custom message instead and force the 'Drop Changes' option.

    Thanks

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply