none
Transaction locks entire table RRS feed

  • Question

  • Hi, I am developing an web service that is connected with MS Access 97 database using Microsoft Jet 4.0 OLEDB and update and insert some data in the database inside transaction statement. On the other hand, I have an MS Access 97 multi-user application that is accessing to the same database. The problem comes when the web service is updating or inserting some row in a table of the database and in the other application, any user update or insert some row (not the same exactly). The application shows the error message 'The table is locked by user XXXX on the machine XXXX'. The error number is 3260. If I remove the transaction statement, there is no problem. It seems that I use transactions, it locks entire table. I hope you help me. Thanks.
    Wednesday, December 3, 2008 9:11 AM

Answers

  • That's how locking works in Access database. Transaction may lock the entire table in Access and there is no control over it. How big is your application? Are you sure that you have right database choice for multiuser environment? You might consider using SQL Server Express edition in this case. It is light version of SQL Server, provider same functionality as the full one, except advanced features, you will get better support for multi-user environment and it is free.

    If you still need to use Access, what exactly are you executing inside of transaction? Is it single SQL statement or not? If it is, then you do not need to use transaction at all.

     

    Wednesday, December 3, 2008 11:22 AM
    Moderator

All replies

  • That's how locking works in Access database. Transaction may lock the entire table in Access and there is no control over it. How big is your application? Are you sure that you have right database choice for multiuser environment? You might consider using SQL Server Express edition in this case. It is light version of SQL Server, provider same functionality as the full one, except advanced features, you will get better support for multi-user environment and it is free.

    If you still need to use Access, what exactly are you executing inside of transaction? Is it single SQL statement or not? If it is, then you do not need to use transaction at all.

     

    Wednesday, December 3, 2008 11:22 AM
    Moderator
  • I have to do it with Access 97, is a constraint of the system. I execute several update and insert statements inside of transaction. The Access 97 database is like a data server and I synchronize data from an application that is running on smartdevice. The smartdevice call to the web service with data and the web service update the database. While this is happening, other user can access to the database to do other actions and if web service is inside transaction, the other user get the error. The question only was to know if it was posible to do it. Thanks for you answer. 
    Wednesday, December 3, 2008 4:32 PM
  • What you can do in your situation is to trap an exception, wait for some predefined period of time and try to resubmit transaction. If your application keeps transactions as short as possible it would minimize failures from the client side. Also try keep connection closed and open it only for the period of time when you need to do any actions against database.

    Thursday, December 4, 2008 11:19 AM
    Moderator