Access SQL Server Insert blocked by Select

问题:

OK we have a multi user (25 users) Access 2013 FE and a SQL Server 2012 BE. Up until yesterday the whole system was working FINE and now it has completely stopped.

• If user A has a record open via a straight forward select query reading from TABLE Z, then if user B tries to do an insert on TABLE Z, they receive a timeout message. When I go to SQL server and run SP_WH02, it states User B is blocked by User A. When I then investigate the command that is blocking user B, it is just a simple SELECT statement.

Does anyone know why this would be?

The form that User A has open has Record Locks = No Locks and Recordset Type = Dynaset

The record source is a SELECT, retrieving two fields where the key field is a parameter based on the value of another.

However, nothing has changed on this system for months, so I'm confused as to why this would happen.

Thanks for any help.

回答1:

Ok we solved it. If anyone else has the same issue: We archived 9400 records into a new table and now we can do inserts again. It has bought us time so going forward I will normalize table Z further and auto archive records against a criteria.

回答2:

It may be the same or similar issue as in MS Access holds locks on table rows indefinitely

Access only fetches the first x rows of the big recordsource, leaving the table in a ASYNC_NETWORK_IO wait state, i.e. locked.

Possible solutions are:

• Don't have forms or queries that select all records. It usually doesn't make too much sense to scroll through 20k+ records.
• Force Access to fetch all records, to release the lock. You can do this with Me.RecordsetClone.MoveLast e.g. in Form_Load(). Only advisable with a fast network connection.