Are check constraints thread safe?

Question

Can check constraint be used instead of pessimistic locks?

Consider there is following table in SQL Server:

create table Balances(
 id int,
 userId int,
 balance money)

If more than 1 thread try to change the balance will check constraint solve any concurrency problem or locks should be used?

Also, I wonder if check constraints has the same behavior in other relational databases such as PostgreSQL?

Answer 1

I'm answering between the lines here, as the question itself doesn't make too much sense, but the comments appear to be adding some context.

I suspect that the real problem isn't the CONSTRAINT (which you should have), but your logic, and that you likely have something like the below:

IF (SELECT Balance - @TransferValue
    FROM dbo.YourTable
    WHERE AccountID = @AccountID) >= 0
BEGIN
    UPDATE dbo.YourTable
    SET Balance = Balance - @TransferValue
    WHERE AccountID = @AccountID;
END;

Regardless of CONSTRAINTS this may well not work as you want if you have 2 threads running at the same time. What we would have then is a potential race condition:

1. Thread 1 checks the value of Balance - @TransferValue and resolves a value >= 0
2. Thread 2, before thread 1 goes to perform the UPDATE, also checks the value of Balance - @TransferValue and resolves a value >= 0
3. Thread 1 UPDATEs the value of Balance reducing it to a value >= 0; locking the row in the process while the transaction completes.
4. Thread 2 waits for Thread 1 to release the lock on the row/table.
5. Thread 1 completes, releasing its lock
6. Thread 2 UPDATES the value of Balance reducing it to a value < 0.

In such a scenario, a CONSTRAINT would stop the 2nd update running, but that doesn't stop the code above being bad. We could, if you really wanted, make it worse, and then the CONSTRAINT wouldn't work at all:

DECLARE @NewBalance decimal(12,4);

SELECT @NewBalance = Balance - @TransferValue
FROM dbo.YourTable
WHERE AccountID = @AccountID;

IF @NewBalance > 0
BEGIN
    UPDATE dbo.YourTable
    SET Balance = @NewBalance
    WHERE AccountID = @AccountID;
END;

Now, the CONSTRAINT wouldn't stop the UPDATE at all. In fact, the value of Balance would be the value of Balance before thread 1 completed minus the Transfer Value of thread 2.

For such a simple UPDATE you would just handle it all in the same statement and use a CONSTRAINT, yes. So the CONSTRAINT would be

ALTER TABLE dbo.YourTable ADD CONSTRAINT chk_YourTable_PositiveBalance CHECK (Balance >= 0);

And then you can just do the following statement:

UPDATE dbo.YourTable
SET Balance = Balance - @TransferValue
WHERE AccountID = @AccountID;

If, however, you need to "go away" and get the value of the balance prior for "reasons" then you need to apply appropriate locking and use transactions. Something like:

SET XACT_ABORT ON; --Aborts the transaction on error, and causes an implicit ROLLBACK

BEGIN TRANSACTION;

DECLARE @NewBalance decimal(12,4);

SELECT @NewBalance = Balance - @TransferValue
FROM dbo.YourTable WITH (UPDLOCK) --You may want WITH(UPDLOCK, SERIALIZABLE), but I am assuming you are effecting a single row here
WHERE AccountID = @AccountID;

--Do something(s)

UPDATE dbo.YourTable
SET Balance = @NewBalance
WHERE AccountID = @AccountID;

--Do something(s) else?

COMMIT;

Then thread 2 won't be able to assign a value to @NewBalance while thread 1 is still doing its work.