Solve Database Concurrency Issues with TypeOrm

I use a pattern in my services where I have methods that always return an Entity but it is up to that method whether to retrieve it from the database or create it newly. I call these methods

getOrCreate*

An example. Users can add a

Tag

Post

TagService

getOrCreateTag(name: string)

This can lead to a problem. When two requests to the backend occur at the exact same time, with the exact same string, two tags with the same name are inserted. (You think it is unlikely? Please read the note below the article!)

What happens in slow-mo:

1. Request A: asks whether that Tag exists
2. Doesn't find it
3. Decides to create it
4. Request B meanwhile asks whether that Tag exists
5. Doesn't find it
6. Decides to create it
7. Request A creates it
8. Request B creates it

You may prevent that easily by a unique key constraint. But you only shift the issue. Because then step 8. will just fail with an exception leaving the application crashing.

One way to fix it, I will describe here implemented with TypeOrm.

Pessimistic lock

A lock of the database is always created during writing to the database. For example, step 7. and 8. both create a super short-living write lock. However, the issue was, that in step 2. and 5. respectively both requests couldn't find it and already decided they are gonna write it to the database.

A pessimistic read lock is something you create manually. In TypeOrm you need the database connection for that.

Repository:

async getOrCreateTag(name: string): Promise<Tag> {
	return this.manager.transaction(
		(entityManager: EntityManager): Promise<Tag> => {
			return entityManager
				.createQueryBuilder(Tag, "tag")
				.setLock("pessimistic_read")                    
				.where({ name})                    
				.getOne()
				.then(async (result) => {
					let tag = result;
					if (undefined === tag) {
						tag = new Tag();
						tag.name = name;	
						return await entityManager.save(tag);
					}
					return tag;
				});
		}
	);
}

So, what it does is, it establishes a transaction (required for locks) and tries to fetch the tag. If not possible (tag is undefined) it is going to create one.

So far so good. If you were running into the issue of two requests trying to create the same entity simultanously, you are now better. But, when you have concurrent requests you will now run into the issue of Deadlocks.

"Deadlock found when trying to get lock; try restarting transaction."

Deadlocks

What happens now in slow-mo:

1. Request A: creates a lock
2. Request A: asks for whether that Tag exists
3. Doesn't find it
4. Decides to create it
5. Request B meanwhile tries to create a lock
6. will find the lock from request A
7. will fail with message above
8. Request A creates it

The message means that you have to restart the transaction. The same as locks are manual, the error catching and restarting is manual.

The Service

async getOrCreateTag(name: string): Promise<Tag> {
        const maxTries = 10;
        let currentTry = 0,
            tryTransaction = true;

        let tag = null;
        while (tryTransaction) {
            currentTry++;

            try {
                tag = await this.tagRepository.getOrCreateTag(
                    name,
                    this.createTagService
                );
            } catch (e) {}

            if (null !== tag) {
                tryTransaction = false; // proceed, because everything is fine
            }

            if (currentTry >= maxTries) {
                throw new Error("Deadlock on getOrCreateTag found.");
            }
        }

        return tag;
    }

That's my solution. If you have a better one, let me know!

Although it is very unlikely, that two users have the same tag entering at the time, it still can be. Imagine one user creates a tag but you happen to fire two backend requests in the frontend when he does that. One to store the tag, one to send a push notification to people who have subscribed to that tag.

There is also other ways with optimistics locks, but I like the idea of letting the service simply "wait" for a split second. I have a monolithic app, so that works fine for me.

Let me know, what you think about this.