MS Access front end SQL 2019 Express backend View locks records.
MS Access front end SQL 2019 Express backend. We have a linked view. Several columns from one table with about 16k records. The view is bound to a read only tabular form. All is well until a user sorts the view. When the view is sorted it places a table lock on the table and other users are locked out of the table for updates. Any ideas where to check? Thanks in advance.
ASKER
Hi Peter,
Please explain. When any suer sorts the form the SQL table is locked for everyone else. How do you "unlock" it. All works fine until someone sorts the view.
Bill
Please explain. When any suer sorts the form the SQL table is locked for everyone else. How do you "unlock" it. All works fine until someone sorts the view.
Bill
Hi,
There should be one lock action happened to the table when user is sorting the table/view.
Do you expect other user to be able to adjust table row/record? You can even change the way (of current form) to use one other duplicated table instead (of original table). Then Other user is able to adjust original table. But for such way, you must ensure all changes made to duplicated table should be also applied to original table.
There should be one lock action happened to the table when user is sorting the table/view.
Do you expect other user to be able to adjust table row/record? You can even change the way (of current form) to use one other duplicated table instead (of original table). Then Other user is able to adjust original table. But for such way, you must ensure all changes made to duplicated table should be also applied to original table.
ASKER
Hi,
Please explain HOW as I don't understand at all what you are explaining. When the table is locked no data can be changed.
The table is usually editable and many users are editing data. If one user sorts the SQL View then the table becomes locked for all users. This is a compiled SQL view.
Please explain HOW as I don't understand at all what you are explaining. When the table is locked no data can be changed.
The table is usually editable and many users are editing data. If one user sorts the SQL View then the table becomes locked for all users. This is a compiled SQL view.
Hi,
Is the form using the view only?
Is the form using the view only?
ASKER
One form uses the view just for look up. A different form is bound to the table. If the form that is bound to the View is sorted the table is locked for some reason and no updates can be made. All works OK until the view is sorted.
Any other experts out there?
Any other experts out there?
Can you adjust the way? Can you sort out the view instead of the table?
If you really have to sort out the table, can you copy table to another new one, and use the new one for the form?
If you really have to sort out the table, can you copy table to another new one, and use the new one for the form?
Check the kind of lock.. normally it is not a lock, but an active transaction. Some sort and scroll actions in Access use a open server-side cursor, thus you'll get blocking processes.
Use a snapshot cursor in Access, instead of dynamic.
Use a snapshot cursor in Access, instead of dynamic.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Make sure you have a unik key on the view
Just for testing, in Access right-click on the view and design, and add a primarykey on a unik field, and save the view.
If the problem is gone, let me know because you have to add the primary key each time you link the view in the database.
Just for testing, in Access right-click on the view and design, and add a primarykey on a unik field, and save the view.
If the problem is gone, let me know because you have to add the primary key each time you link the view in the database.
No. Don't use NOLOCK. It can lead to wrong data returned by your view.
ASKER
Please explain. It looks OK in testing.
NOLOCK ignores locks. Thus you can read data which is already deleted or you read uncommitted data. As it a transactional system, this means that you may read data which should not be read at this point in time. Or you when you read the view during a reorg, then you may even miss entire data pages (8k), this can mean a lot of rows.
NOLOCK is only the last option in such cases.
The first thing is to use a snapshot, client side cursor.
When your issues then still occurs, then you need to look into the indices of the used tables. Are the clustered indices the appropriate ones? Do supporting or covering indices for your view exists?
Also consider using a stored procedure and a passthrough query instead.
And why displaying 16k rows to the user? I cannot work with such lists.
NOLOCK is only the last option in such cases.
The first thing is to use a snapshot, client side cursor.
When your issues then still occurs, then you need to look into the indices of the used tables. Are the clustered indices the appropriate ones? Do supporting or covering indices for your view exists?
Also consider using a stored procedure and a passthrough query instead.
And why displaying 16k rows to the user? I cannot work with such lists.
ASKER
Hi ste5an,
I am learning more all the time and did not know that. This is a seldom used search screen so users can find historical data. They can search across many columns for various criteria. I'm not worried about wrong data display as this is just a look up screen. I tried the snapshot on the form but no help.
It seems that any query I create from the view in MS Access locks the table.
The clustered PK is used in hundreds of reports and screens so design change to the underlying table is not possible.
Perhaps I can use an SP with a pass-through query. I'll give it a try or perhaps a local temp table. I appreciate your input!
I am learning more all the time and did not know that. This is a seldom used search screen so users can find historical data. They can search across many columns for various criteria. I'm not worried about wrong data display as this is just a look up screen. I tried the snapshot on the form but no help.
It seems that any query I create from the view in MS Access locks the table.
The clustered PK is used in hundreds of reports and screens so design change to the underlying table is not possible.
Perhaps I can use an SP with a pass-through query. I'll give it a try or perhaps a local temp table. I appreciate your input!
ASKER
Hi ste5an,
How do I write this in a stored procedure? "snapshot, client side cursor".
Thanks!
How do I write this in a stored procedure? "snapshot, client side cursor".
Thanks!
Just be aware that with a snapshot cursor, you are asking for a complete copy of the result set to be made before the result is returned.
That can lead to a considerable delay depending on the size of the result set.
Jim.
That can lead to a considerable delay depending on the size of the result set.
Jim.
Can you unlock the physical table, if you expect to do update on the table? This is the thing you should do.