Ensuring atomic updates on tables with specific logical constraints

I have a table like this (example):

id       INT     PRIMARY KEY
number   BIGINT  UNIQUE
type     ENUM
user_id  INT

number is the primary data for this table, representing a unique number of which only one can exist. These numbers can be of type A or B. A user can claim these numbers, in which case the user_id will be filled in (until then it's NULL).

The specific logical constraints on this are that a number can only be claimed once and a user can only claim one number of type A, but unlimited numbers of type B.

To ensure that a user can only claim one number of type A, a UNIQUE (t开发者_StackOverflowype, user_id) constraint would be fine. That would prevent the user from claiming an unlimited number of B numbers though.

Currently I'm handling this on the application level like this:

SELECT COUNT(*) FROM `table` WHERE `type` = 'A' AND `user_id` = ?

If the count is not 0, abort, else:

UPDATE `table` SET `user_id` = ? WHERE `type` = 'A' AND `user_id` IS NULL LIMIT 1

But there's still a tiny chance of a race condition in here whereby a user would get two numbers of type A.

How can I formulate a constraint or atomic update that ensures that a user can only claim one number of type A? Would stored procedures, triggers and such help here at all? Is this possible in MySQL without restructuring the schema?

Just use SELECT ... **FOR UPDATE**

SELECT * FROM `table` WHERE `type` = 'A' AND `user_id` = ? FOR UPDATE

Not sure if it works for COUNT(*) but it is easy to check.

If it is ok to add another column - then you can add something like if_type_A and maintain it with trigger: if type is A - then 1, else - null. And apply unique constraint for user_id + if_type_A composite key

Slightly different approach. Use one table to record the numbers and their types, one table to record claimed numbers of type 'a', and one table to record claimed numbers of type 'b'. Type 'a' and type 'b' numbers have different logical constraints; it makes perfectly good sense to store them in different tables.

create table unique_numbers (
  n integer primary key,
  n_type char(1) default 'b' check (n_type in ('a', 'b')),
  unique (n, n_type)
);

create table claimed_type_a_numbers (
  n integer primary key,
  n_type char(1) not null default 'a' check (n_type = 'a'),
  user_id integer not null unique, -- references users, not shown
  foreign key (n, n_type) references unique_numbers (n, n_type)
);

create table claimed_type_b_numbers (
  n integer primary key,
  n_type char(1) not null default 'b' check (n_type = 'b'),
  user_id integer not null, -- references users, not shown
  foreign key (n, n_type) references unique_numbers (n, n_type)
);

But MySQL doesn't support CHECK constraints. ("The CHECK clause is parsed but ignored by all storage engines.") So you'll probably want to implement these CHECK constraints with MySQL triggers.

If your schema for ENUM allows nulls, you won't have to restructure the schema, but the application will require updates for this solution:

Use null to represent "B"-type numbers. Then you can put a unique constraint on (type, user_id). MySQL will allow multiple (NULL, user_id) entries in an index.