We know what is going on in the sense that:
If We run the following in Query Analyser:
SELECT SERIALNO, TITLE, FIRSTNAME, SURNAME FROM
CONTACT WHERE SURNAME LIKE 'A%'
We get a shared table level lock on the CONTACT table
This will block any update process on the CONTACT table
e.g. UPDATE CONTACT SET SURNAME = 'Smith' FROM CONTACT WHERE SERIALNO = '1000'
until the SELECT has completed. Obviously, if the select statement completes quickly then there's not much of a problem as the update will not have to wait long. However, if the SELECT is slow (for instance we have de-duplication routines that use complex where clauses) then we get problems.