As I tester I come across a lot on this anti pattern. And in the name of all web applications in the world I beg all the world developers: “Please, do not do that!”.
What is this anti patter all about? For example you have following database table:
CREATE TABLE `tbl_anti_pattern` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`CodeA` char(9) NOT NULL
PRIMARY KEY (`Id`),
UNIQUE KEY `idx_code_a` (`CodeA`),
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
Please note the unique key idx_code_a. Developer has requirement that table records need to be unique regarding the CodeA column. And then developer, in order to check column uniqueness, prior to database insert, does following check:
select Id from tbl_anti_pattern where CodeA = code_variable.
If returned number of rows is 0, than does following insert using prepared statement:
insert into tbl_anti_pattern (CodeA) values(?);
Of course, it does not catch unique index exception. Why I should do that when I previously checked uniqueness using select statement?
This code will perfectly work in single user environment (which is true for the most unsuccessful web applications).
But successful web application have a thousands of concurrent users, and in that environment previous code will fail big time.
Why? Because unique check has to be in one transaction, not two. You want database engine (which is c code) to do the uniqueness check, because uniqueness check on insert does table lock for the time when the check is performed. Other users will have to wait with their inserts (this is done by database engine automatically) until current insert finishes its job. Using select is not good because when select finishes, between select and insert there could be another user (transaction) doing the insert on the same table.
What is the point of this post? Learn your tools (database engine) and use them properly. Do not reinvent the wheel.
