Why is this query generating a PK violation error?

Is this table truncated or the rows deleted in some moment? And how often? It makes sense to me that the rows should not be found in some moment, as you're running "insert if not exists", and in this moment two or more queries may hit the database to insert the same data... only one will... the other should do nothing if the row was inserted before its "not exists" look up, or fail if the row was inserted after the look up.

I have only an Oracle database right now to do some tests and I can reproduce this problem. My commit mode is explicit:

• Create the empty table, the unique constraint and grant select, insert to another user:

  CREATE TABLE just_a_test (val NUMBER(3,0));
  
  ALTER TABLE just_a_test ADD CONSTRAINT foobar UNIQUE (val);
  
  GRANT SELECT, INSERT ON just_a_test TO user2;
  

• DB session on user1:

  INSERT INTO just_a_test 
  SELECT 10 
  FROM DUAL 
  WHERE NOT EXISTS 
  (
    SELECT 1 
    FROM just_a_test 
    WHERE val = 10
  )
  ;
  
  -- no commit yet...
  

• DB session on user2:

  INSERT INTO user1.just_a_test 
  SELECT 10 
  FROM DUAL 
  WHERE NOT EXISTS 
  (
    SELECT 1 
    FROM user1.just_a_test 
    WHERE val = 10
  )
  ;
  
  
  -- no commit yet, the db just hangs til the other session commit...
  

So I commit the first transaction, inserting the row, and then I get the following error on user2 session:

"unique constraint violated"
*Cause:    An UPDATE or INSERT statement attempted to insert a duplicate key.
           For Trusted Oracle configured in DBMS MAC mode, you may see
           this message if a duplicate entry exists at a different level.

Now I rollback the second transaction and run again the same insert on user2 and now I get the following output:

0 rows inserted.

Probably your scenario is just like this one. Hope it helps.

EDIT

I'm sorry. You asked two questions and I answered only the Could someone explain why/how this is happening? one. So I missed What would be an efficient way to safely insert in one go (in other words, in a single query)?.

What exactly means "safely" for you? Let's say you're running an INSERT/SELECT of lots of rows and just one of them is duplicated compared to the stored ones. For your "safety" level you should ignore all rows being inserted or ignore only the duplicated, storing the others?

Again, I don't have a SQL Server right now to give it a try, but looks like you can tell SQL Server whether to deny all rows being inserted in case of any dup or deny only the dups, keeping the others. The same is valid for an insert of a single row... if it's a dup, throw an error... or just ignore it in the other hand.

The syntax should look like this to ignore dup rows only and throw no errors:

    ALTER TABLE [TableName] REBUILD WITH (IGNORE_DUP_KEY = ON);

By default this option is OFF, which means SQL Server throws an error and discards non dup rows being inserted as well.

This way you would keep your INSERT/SELECT syntax, which looks good imo.

Hope it helps.

Sources:

https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-table-index-option-transact-sql?view=sql-server-2008

https://stackoverflow.com/a/11207687/1977836

It might help to be explicit about this. The below runs this in an explicit transaction, locks the row explicitly.

DECLARE @user_id INT; SET @user_id=29851;
DECLARE @role_id INT; SET @role_id=1;

BEGIN TRY
    BEGIN TRANSACTION;

    DECLARE @exists INT;
    SELECT @exists=1 
    FROM [dbo].[users_roles] WITH(ROWLOCK,HOLDLOCK,XLOCK)
    WHERE user_id=@user_id AND role_id=@role_id;

    IF @exists IS NULL
    BEGIN
        INSERT INTO [dbo].[users_roles] ([user_id], [role_id])
        VALUES(@user_id,@role_id);
    END

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
END CATCH