CODEWITHSUNDEEP
sql-serverms-accessvbams-access-2010sp-who2
dmc2005
dmc2005

Reputation: 595

why does "SELECT 1 from <table>" cause a LCK_M_IX on another process doing a DELETE

I have a table listing patient_clinic_visits. I have SQLSERVER 2005 backend. Access2010 frontend.

Each morning this table needed refreshing from a data dump from the hospital mainframe.

this data dump includes information on the day before (who attended, didn't, cancelled) and forward appointments for next 6 weeks.

I therefore do a "DELETE * from Patient_clinic_visits where Visit_Date > (a day) and < (a day + 1) to clean out the table for each day in turn before uploading the new data after some processing.

On most days there will be only about 100-150 records in each day. Their is one foreign key to Pat_ID in Master_Patient_Table which has NO_ACTION chain on it.

At the moment this Delete query is timing-out in the Access frontend after processing a couple of days of data (ie it works okay for Monday, Tuesday, Wednesday...)

I run sp_whoisactive and get:

00 01:53:01.926 52 [[query SELECT 1 FROM "dbo"."Patient_Clinic_Visits" ]] RAHCC_User (265ms)ASYNC_NETWORK_IO 0 0 0 NULL 51 0 0 2 suspended 0 NULL SAH0020663 RAHCC_DB Microsoft MDB RAHCC 2013-04-02 09:08:33.027 0 2013-04-02 11:01:35.033

00 00:00:27.610 53 [[query -- DELETE from Patient_Clinic_Visits WHERE clinic_date >= '26-Mar-2013' AND clinic_date < '27-Mar-2013' AND Clinic_location = 'MONC' ]] HAD\jhogan05 (27596ms)LCK_M_IX 16 0 0 52 1,074 0 0 130 suspended 2 NULL SAH0048645 RAHCC_DB Microsoft SQL Server Management Studio Express - Query 2013-04-02 11:01:07.343 0 2013-04-02 11:01:35.033

This indicates my client front end is waiting on a "SELECT 1 from Patient_Clinic_Vists" and this is blocking the procedure. Apparently this is due to an ASYNC_NETWORK_IO on the client (which can happen with Access front ends doing a table request and then not processing the data).

a) However a "SELECT 1 from Patient_Clinic_Visits" should really only return TRUE or FALSE ?? which is unlikely to fill up anything, and unclear why it would cause a block situation??

b) I can't find "SELECT 1..." in my Access frontend anywhere.. Is this perhaps part of a sequence of sub-selects made by SQLSERVER in response to a more complext select? If so how do I find the true select causing this situation in that process history?

cheers,

JonHD

Upvotes: 1

Views: 2875

Answers (1)

chris
chris

Reputation: 638

a) The query "SELECT 1 from Patient_Clinic_Visits" will return either an empty result set (if the Patient_Clinic_Visits table is empty) or a result set with as many rows as the Patient_Clinic_Visits table, each row having a 1 in it. To do this, SQL Server will have to issue a query against the whole Patient_Clinic_Visits table, which (assuming default locking behavior) will cause shared (read) locks to be issues against the rows in that table.

b) (NOTE: the OP addressed this point in the comments) I might be missing something, but I don't see where you come up with the "SELECT 1 from Patient_Clinic_Vists" query based on sp_whosactive. The best way to understand the SQL being sent from your application to the database server might be to use SQL Profiler with an appropriate filter (perhaps filtering only for connections from the host running your application).

Upvotes: 1

Related Questions