Improve locks in domains and tags tables when short URLs are created

[acelaya]

Currently, when a short URL is created, it can be linked to a domain and/or list of tags. Those two are created if they don't exist.

However, if two short URLs are tried to be created linked to new tags/domains, both requests run into a race condition that can end up in a fatal error.

The tags/domains tables should be locked by doing a select for update query when checking if they exist, so that these race conditions do not happen.

Once the new entities have been created, the lock can be dropped, unlocking other requests that are waiting.

---

UPDATE: After some investigation, this task is turning harder than expected. SELECT ... FOR UPDATE does not set the locks properly when the entries do not exist (yes, it's not called SELECT ... FOR INSERT for a reason).

That means the lock would need to be set using an external approach, instead of the native database mechanism.

This article explain the problem in detail: https://mysqlquicksand.wordpress.com/2019/12/20/select-for-update-on-non-existent-rows/

[acelaya]

Blocked by #862

[acelaya]

Actually, the issue described in #835 (comment) is probably not caused by a missing locking, but probably by the fact that imported urls are flushed in batches.

If during the same batch the same tag/domain is used for several urls and it didn't exist yet, it will try to be created every time, causing the exception when the flush happens.

In order to solve this, we need to track tags and domains somehow, so that the same references are resolved during the batch.

The locking is also needed, but that will solve a different issue.

[antwonw]

So, I believe this is the fatal error you're referring to?

When importing and the domain is not otherwise known/specified in Shlink:

In AbstractSQLiteDriver.php line 48:
                                                                                                               
  An exception occurred while executing 'INSERT INTO domains (authority) VALUES (?)' with params ["glnr.in"]:  
                                                                                                               
  SQLSTATE[23000]: Integrity constraint violation: 19 UNIQUE constraint failed: domains.authority              
                                                                                                               

In Exception.php line 18:
                                                                                                   
  SQLSTATE[23000]: Integrity constraint violation: 19 UNIQUE constraint failed: domains.authority  
                                                                                                   

In PDOStatement.php line 115:
                                                                                                   
  SQLSTATE[23000]: Integrity constraint violation: 19 UNIQUE constraint failed: domains.authority  

When importing and the tag is not otherwise known/specified in Shlink:

In AbstractSQLiteDriver.php line 48:
                                                                                                         
  An exception occurred while executing 'INSERT INTO tags (name) VALUES (?)' with params ["instagram"]:  
                                                                                                         
  SQLSTATE[23000]: Integrity constraint violation: 19 UNIQUE constraint failed: tags.name                
                                                                                                         

In Exception.php line 18:
                                                                                           
  SQLSTATE[23000]: Integrity constraint violation: 19 UNIQUE constraint failed: tags.name  
                                                                                           

In PDOStatement.php line 115:
                                                                                           
  SQLSTATE[23000]: Integrity constraint violation: 19 UNIQUE constraint failed: tags.name  
                                                                                           

[acelaya]

Yes, but that has already been fixed here #1068

This ticket is for something else.

[P1erreGaultier]

Hello, I am trying Shlink and I experience this issue.
I use 3 shlink instances with one Postgres database and a redis to shared locks.

When I try to create two links with a tag that doesn't exist (two calls to /short-urls endpoint at the same time), it fails with database unicity constraint error.

Since this issue is not resolved, I managed to avoid this situation as much as possible, but I still have errors sometime.
Do you know if there is a workaround to prevent error on tags creation ?

Thank you for your work on Shlink !

Pierre

[acelaya]

Nop, nothing to do for now until this is fixed.

It shouldn't happen too often, unless you have a heavy short URL creation load.

[acelaya]

Shlink now includes its own external locks to make sure more than one short URL being created in parallel with the same new domain and/or tag/s, does not result in a duplicated key error.

This will be shipped with Shlink 3.6.0, and will require redis to properly work on a multi-instance set-up.