In SQL Server, in layman's terms, how do you lock resources well enough to perform an INSERT-IF-NOT-EXISTS transaction?
Option 1: take a lock out which locks at least the range in the index that the row would exist in.
SET XACT_ABORT ON;
BEGIN TRAN
IF NOT EXISTS(SELECT 1 FROM [TheTable] WITH (UPDLOCK, HOLDLOCK) WHERE [ColumnX] = @valX)
    INSERT [TheTable] ([ColumnX]) VALUES (@valX)
COMMIT

HOLDLOCK will give serializable semantics and lock the range between existing keys in the index where the value would fit (if there is a suitable index, otherwise it will lock the whole table). UPDLOCK reduces the probability of deadlocks in this pattern as two concurrent queries can't take out the same range lock in the reading phase.
Option 2: You can just add a unique constraint on ColumnX and try the insert anyway and catch the error raised from duplicate key violation.
Given that Option 1 needs an index with leading column ColumnX anyway to meet your preference of not &quot;locking the whole table every time&quot; you might as well add one and define it as unique. The index will speed up the existence check anyway. With that in place I would select between option 1 and 2 on the basis of how frequently I expect attempts to insert duplicates.