Locking Potential Transactions in PostgreSQL-CodePudding

Well, Sorry, if you find this question weird, But Let me ask It anyway. Imagine the following situation. There is 2 Clients, A and B. The A Client decided to create Profile and the Transaction in general takes 2 Minutes until Completion for example.

After 1 minute, B Client Decided to create a Profile with THE SAME Username And Password, (but the first Transaction is still in the Process, And we cannot apply the unique constraint, because there is no such User with this Username quite yet.)

So It will eventually end up with UNIQUE CONSTRAINT exception, and we'll need to make a rollback.

The Question is: How to avoid this situation?

I've heard about LOCK in PostgreSQL (that allows to lock the EXISTING ROW in order to others can't change it or read) but haven't find any similar to this sort of case.

Is there any feature, that provides some sort of functionality to block potential transactions?

CodePudding user response：

Start the transaction like this:

BEGIN;
SET lock_timeout = 1;
INSERT INTO users (username, password) VALUES (...);
RESET lock_timeout;
/* the rest of the transaction */
COMMIT;

The second transaction that tries to create the same user won't block, but fail right away and can be rolled back.

CodePudding user response：

EDIT: in case someone else stumbles across this, Laurenz Albe's post is a better solution. Use that instead.

The Question is: How to avoid this situation?

A simple way would be to split the commit into two parts, probably using a savepoint:

func createUser(user: User) error {
    this.db.exec('INSERT INTO users VALUES ($1, $2)', user.username, user.hashedPassword);
    this.db.withTransaction(func (tx Transaction) {
        tx.exec('DELETE FROM users WHERE username = $1', user.username);
        sp = tx.createSavepoint();
        tx.exec('INSERT INTO users VALUES ($1, $2)', user.username, user.hashedPassword);
        try {
            // your code that takes two minutes
            tx.commit();
        } catch (e) {
            tx.rollbackToSavepoint(sp);
            tx.commit();
        }           
    });
}

Where you first insert your row, immediately commiting the change. Now any new user can't use that username.

Then, start a transaction, delete the user. Create a savepoint. Create the user again. Now, instead of rolling back the entire transaction if something fails, rollback to the savepoint (where the user was created then deleted, effectively a no-op). If it works, since you deleted then created again, then the delete was effectively a no-op.