title: Primary key conflict even when TABLOCKX and HOLDLOCK hints
body: <p>I have a table which is used to create locks with unique key to control execution of a critical section over multiple servers, i.e. only one thread at a time from all the web servers can enter that critical section.</p>
  
  <p>The lock mechanism starts by trying to add a record to the database, and if successful it enters the region, otherwise it waits. When it exits the critical section, it removes that key from the table. I have the following procedure for this:</p>
  
  <pre><code>SET TRANSACTION ISOLATION LEVEL READ COMMITTED
  BEGIN TRANSACTION
    DECLARE @startTime DATETIME2
    DECLARE @lockStatus INT
    DECLARE @lockTime INT
    SET @startTime = GETUTCDATE()
  
    IF EXISTS (SELECT * FROM GuidLocks WITH (TABLOCKX, HOLDLOCK) WHERE Id = @lockName)
    BEGIN
      SET @lockStatus = 0
    END
    ELSE
    BEGIN
      INSERT INTO GuidLocks VALUES (@lockName, GETUTCDATE())
      SET @lockStatus = 1
    END
  
    SET @lockTime = (SELECT DATEDIFF(millisecond, @startTime, GETUTCDATE()))
    SELECT @lockStatus AS Status, @lockTime AS Duration
  COMMIT TRANSACTION GetLock
  </code></pre>
  
  <p>So I do a <code>SELECT</code> on the table and use <code>TABLOCKX</code> and <code>HOLDLOCK</code> so I get an exclusive lock on the complete table and hold it until the end of the transaction. Then depending on the result, I either return fail status (0), or create a new record and return (1).</p>
  
  <p>However, I am getting this exception from time to time and I just don't know how it is happening:</p>
  
  <blockquote>
    <p>System.Data.SqlClient.SqlException: Violation of PRIMARY KEY constraint 'PK_GuidLocks'. Cannot insert duplicate key in object 'dbo.GuidLocks'. The duplicate key value is (XXXXXXXXX). The statement has been terminated.</p>
  </blockquote>
  
  <p>Any idea how this is happening? How is it possible that two threads managed to obtain an exclusive lock on the same table and tried to insert rows at the same time?</p>
  
  <p><strong>UPDATE</strong>: It looks readers might have not fully understand my question here, so I would like to elaborate: My understanding is that using TABLOCKX obtains an <strong>exclusive</strong> lock on the table. I also understood from the documentation (and I could be mistaken) that if I use the HOLDLOCK statement, then the lock will be held till the end of the transaction, which in this case, I assume (and apparently my assumption is wrong, but that's what I understood from the documentation) is the outer transaction initiated by the <code>BEGIN TRANSACTION</code> statement and ended by <code>COMMIT TRANSACTION</code> statement. So the way I understand things here is that by the time SQL Server reach the SELECT statement having the TABLOCKX and HOLDLOCK, it will try to obtain an exclusive lock on the whole table, and will not release it until the execution of <code>COMMIT TRANSACTION</code>. If that's the case, how comes two threads seam to be trying to execute the same INSERT statement at the same time?</p>