How to implement the multiple NULL check transaction in mysql

Question

I want to implement the NULL check logic in the MYSQL. Here is the code:

mysql> create table temp
(
id int,
des varchar(100),
primary key (id)
);

mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+

mysql> start transcation;

mysql> select * from temp where id=0;
Empty set (0.03 sec)

mysql> insert temp (id,des) values(0,'0');
Query OK, 1 row affected (0.11 sec)

mysql> commit;
Query OK, 0 rows affected (0.02 sec)

It seems well.

However, there is the possibility of multiple NULL check transaction at the same time for my situation.

Trans 1:                                             Trans 2:

mysql> start transaction;                            mysql> start transaction;         

mysql> select * from temp where id=0;
Empty set (0.03 sec)

                                                    mysql> select * from temp where id=0;
                                                    Empty set (0.03 sec)

mysql> insert temp (id,des) values(0,'0');
Query OK, 1 row affected (0.11 sec)

                                                    mysql> insert temp (id,des) values(0,'0');


mysql> commit;
Query OK, 0 rows affected (0.02 sec)

                                                    --block and waiting for the Trans 1 commit;
                                                    ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY'

Trans 2 will report the ERROR 1062 when Trans 1 commit. I want to avoid the Error and I think it is a universal phenomenon of NULL check in the application.

How to implement the multiple NULL check transaction in mysql in the correct way? Are there any ways to block each other when using the "select" sql in the multiple transcation?

Thank you.

update 2016.07.12

I just simplify the situation I meet with above. In fact, the table I have is similar to

mysql> create table temp
(
id int NOT NULL AUTO_INCREMENT,
des varchar(100),
unique_id int,
primary key (id),
UNIQUE (unique_id)
);

And my transactions are

Trans 1:                                            Trans 2:

mysql> start transaction;                           mysql> start transaction;         

mysql> select * from temp where unique_id=0;
Empty set (0.06 sec)

                                                    mysql> select * from temp where unique_id=0;
                                                    Empty set (0.02 sec)

mysql> insert temp(des,unique_id) values('0',0);
Query OK, 1 row affected (0.20 sec)

                                                    mysql> insert temp(des,unique_id) values('0',0);


mysql> commit;
Query OK, 0 rows affected (0.02 sec)

                                                    --block and waiting for the Trans 1 commit;
                                                    ERROR 1062 (23000): Duplicate entry '0' for key 'unique_id'

So the PRIMARY KEY is AUTO_INCREMENT in my real case.

score 0 · Answer 1 · answered Jul 11 '16 at 05:59

0

If you insist on inserting the primary key yourself, as opposed to having it autoincrement, then this is exactly the expected and desired behaviour that transactions were designed to create.

If you would like Trans 2 to commit, then you need to either not specify the primary key, or handle the primary key allocation in your application.

answered Jul 11 '16 at 05:59

dotancohen

30,064
36
138
197

I'm not sure what you would like to do. What is "NULL check transaction" in this context? **When you try to commit the same primary key a second time, what is your desired outcome?** – dotancohen Jul 12 '16 at 06:27
I just have a webapp and there is the "user register page". When the user want to register the username, the webapp will check whether the same username is in the database. The "NULL check transaction" is to check whether the specified username is in the database. I simulate the situation above if two users want to register the same username in the 2 transactions. – zhfkt Jul 12 '16 at 07:40
In this case MySQL is working as intended. It is not letting the second user register the taken username. I've actually run into this in production, on popular services whenever a new celebrity or fictional character becomes popular people race to register their name as a username. **You work around this by not using transactions.** Simply let the `INSERT` fail and tell the second user that the name is no longer available. – dotancohen Jul 12 '16 at 07:57
1

You might want to look up the difference between the [LBYL and BAPF philosophies](http://stackoverflow.com/questions/11360858/what-is-the-eafp-principle-in-python) from the Python way of doing things, which do apply to other languages such as PHP. – dotancohen Jul 12 '16 at 07:59
OK. Thank you for the link LBYL – zhfkt Jul 12 '16 at 09:47

How to implement the multiple NULL check transaction in mysql

1 Answers1