MySQL Repeatable Read and Phantoms, unique username example

Question

Martin Kleppmann in his book "Designing Data-Intensive Applications" is showcasing the following problem:

Claiming a username
On a website where each user has a unique username, two users may try to create
accounts with the same username at the same time. You may use a transaction to
check whether a name is taken and, if not, create an account with that name.
However, like in the previous examples, that is not safe under snapshot isolation.
Fortunately, a unique constraint is a simple solution here (the second transaction
that tries to register the username will be aborted due to violating the constraint).

I have a very similar use case, where 2 transactions are trying to claim the name of the entity.

At the beginning of each transaction, I run a select to see if such name was already taken. If it wasn't - create or update, depending on the operation requested by the user. This logic crumbles under concurrent attempts to claim/modify the name.

I am trying to see if there is a mechanism that allows implementing correct behavior under the Repeatable Read isolation level. Unique constraint violation thrown by the DB is not acceptable in my case, neither is a downgrade to Serializable execution.

Can I employ Select For ... Update here? Obviously, I won't be locking the concrete rows, but rather an entire table (correct me if I am wrong in my assumption) as I will not have pk index columns in the WHERE subclause?

Table structure:

   CREATE TABLE `application_domains` (
      `id` varchar(255) NOT NULL,
      `name` varchar(255) NOT NULL,
      `description` varchar(10000) DEFAULT NULL,
      `org_id` varchar(255) NOT NULL,
      `created_time` bigint(20) NOT NULL,
      `updated_time` bigint(20) NOT NULL,
      `created_by` varchar(16) NOT NULL,
      `changed_by` varchar(16) NOT NULL,
      `revision_id` varchar(16) DEFAULT NULL,
      `topic_domain` varchar(255) NOT NULL,
      `enforce_unique_topic_names` tinyint(1) NOT NULL DEFAULT '1',
      `sample_id` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `UK_orgId_name` (`org_id`,`name`),
      UNIQUE KEY `UK_orgId_sampleId` (`org_id`,`sample_id`),
      KEY `FK_references_application_domains_organization` (`org_id`),
      KEY `FK_app_domain_samples_id_references_application_domains_tbl` (`sample_id`),
      CONSTRAINT `FK_app_domain_samples_id_references_application_domains_tbl` FOREIGN KEY (`sample_id`) REFERENCES `application_domain_samples` (`id`) ON DELETE SET NULL ON UPDATE SET NULL,
      CONSTRAINT `FK_references_application_domains_organization` FOREIGN KEY (`org_id`) REFERENCES `organizations` (`org_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

An `INSERT` on a unique contraint `user` field in the table and catching the error is the simplest and most prefered way. If you need to do something more involved then you can `SELECT FOR UPDATE` which does row locking of the rows gaps around the insertion. Why is a Unique constraint violation unacceptable? Can you show your table /index structure (`SHOW CREATE TABLE...`)? — danblack, Sep 15 '21 at 20:47
'On Duplicate only works with unique constaraints, so you need to have one or else this will not trigger — nbk, Sep 15 '21 at 20:50
`UNIQUE KEY `UK_orgId_name` (`org_id`,`name`),` this unique key constraint is breeding a violatioin. — Ihor M., Sep 15 '21 at 21:02
Catch the error n your gui and let the user select a new name, give him some free alternatives — nbk, Sep 15 '21 at 21:30
If I understand you correctly, you are trying to implement a slower version of an actual table lock: instead of setting a simple meta lock on the table that harms concurrency (which is why it has been outdated by row locks to allow more concurrency), you want to lock all rows individually, but equally harming concurrency - and actually requiring special consideration for the special case of empty tables. — Solarflare, Sep 15 '21 at 22:29
What I tried to say is: you appearently want to use `Select For ... Update` on all rows, to lock "rather an entire table". This takes longer if you have more rows. A simple "lock tables for write" is a single meta lock, which takes a constant (small) amount of time, with a similar effect. It has side effects, so you may want to implement it slightly differently, but I guess my point is: if what you are trying to do is actually a table lock, make a table lock, and not n row locks. (Although I would also strongly second the suggestion to use a unique key and handle the violation). — Solarflare, Sep 15 '21 at 23:56

MySQL Repeatable Read and Phantoms, unique username example

0 Answers0