Query locking

Igor Korot 51 Reputation points
2025-02-02T06:37:20.0466667+00:00

Hi, ALL,

I am trying to run the following query thru SQLPrepare/SQLBind/SQLExecute sequence:

INSERT INTO abcattbl WITH( TABLOCK ) SELECT ?, ?, (SELECT object_id FROM sys.objects o, sys.schemas s WHERE s.schema_id = o.schema_id AND o.name = ? AND s.name = ?),  '', 8, 400, 'N', 0, 0, 34, 0, 'MS Sans Serif', 8, 400, 'N', 0, 0, 34, 0, 'MS Sans Serif', 8, 400, 'N', 0, 0, 34, 0, 'MS Sans Serif', '' WHERE NOT EXISTS(SELECT * FROM dbo.abcattbl WHERE abt_tnam=? AND abt_ownr=?);

However trying to do SQLExecute the table becomes locked and the code runs indefinitely.

What am I doing wrong?

I'm trying to make sure there is only 1 record present in the table,

Thank you.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,437 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Erland Sommarskog 116.5K Reputation points MVP
    2025-02-02T10:31:33.6433333+00:00

    You can use my beta_lockinfo to investigate what is blocking what.

    The way to ensure that you don't have duplicate rows in the table is by using proper primary keys. TABLOCK is a sledgehammer that is way too big for the purpose.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.