Have a users table, fields are respectively, id, username, password, and record the user's id (primary key), the user name, and password,

my business logic for the user name field, you must not have repeat, or the whole system is completely confused,


Way is before me, in the control layer, before inserting new users, to judge the first user name, if it exists already? If you already exists, an error prompt to the user, if not, then insert operation,

Pseudo code is as follows:

Transaction start
If (xxxDao. IsUsernameExist (" love ")) {
Return "the user name you entered has been occupied, please try other user name, thank you!" ;
/* * use SQL statements to:
Select * from the user where the username='love'
*/
} else {
XxxDao. InsertUser (user);
Return "user registration, please login to return to the login page." ;
/* * use SQL statements to:
INSERT INTO user (id, username, password) VALUES (' XXXX ', 'love', 'XXXXXXX)
*/
}
End of the transaction



I test at the beginning, this program is running very perfect, what also have no!
But later, problem, ruthless come,,,

When you have more than one person, such as 100 people, concurrent access, are problems:

The 100 comrade, registered the user name "love" at the same time, so they first perform the if (xxxDao. IsUsernameExist (" love ")) this statement, to judge the user list, whether there is love this username, so their results at the same time, there is no, you can register the user name, and then began to perform the INSERT INTO... Operation, the result is my user list, there were 100 user name is a record of your love,,,


It is finished,,

So I want to ask everybody a great god, if I put the code to do a little modification, modification as follows:

Transaction start
If (xxxDao. IsUsernameExist (" love ")) {
Return "the user name you entered has been occupied, please try other user name, thank you!" ;
/* * use SQL statements to:
Select * from the user where the username='love'
*/
} else {
XxxDao. InsertUser (user);
Return "user registration, please login to return to the login page." ;
/* * use SQL statements to:
INSERT INTO user (id, username, password) SELECT 'XXXX', 'love', 'XXXXXXX FROM DUAL WHERE NOT the EXISTS (SELECT * FROM the user WHERE the username=' love ')
*/
}
End of the transaction


If so, will there are more than the concurrency issues? Great god guide me, as far as possible can not lock won't go with lock, and can not go to modify the transaction isolation level, just try not to modify, unless there is no way

CodePudding user response:

To achieve concurrency is not a problem, must do ion level, only logic code cannot prevent concurrent, so must use lock,
This lock has a skill, "dynamic" lock, lock the username to use this variable to do synchronized (username. Intern ()) {XXXXX}, and after the username intern () method calls, so you can put the same user name concurrent queue, as is not the same without queuing, (add on query judgment)

CodePudding user response:

Use stored procedures or row-level locks

CodePudding user response:

reference 1st floor bichir response:

to achieve concurrency is not a problem, must do ion level, only logic code cannot prevent concurrent, so must use lock,
This lock has a skill, "dynamic" lock, lock the username to use this variable to do synchronized (username. Intern ()) {XXXXX}, and after the username intern () method calls, so you can put the same user name concurrent queue, as is not the same without queuing, (add on query judgment)

Say good, very good ah

CodePudding user response:

Database tables set the username is unique, repeated injection will be inserted into the failure, only one can insert success

CodePudding user response:

Positive solution # 4 floor, and the only index

CodePudding user response:

Program logic judgment alone is not enough ~ ~ username field and the only constraints including mysql locking mechanism can prevent concurrency issues ~ reference: http://blog.csdn.net/speedme/article/details/48525119

CodePudding user response:

Add a synchronized lock in isUsernameExist method, can achieve 100 individual method but one by one check

CodePudding user response:

And the only constraint is not truth, only guarantee database good disorderly, but means that the registration page will display, database without corresponding to insert the data,

CodePudding user response:

refer to the eighth floor xiekuntarena response:

add constraints not only truth, only guarantee database good disorderly, but means that the registration page will display, database without corresponding to insert the data,

Not have returned to the influence of the number of rows

CodePudding user response:

I think add a unique constraint is completely solved

CodePudding user response:

references 9 f qq_21873571 response:

Quote: refer to the eighth floor xiekuntarena response:

And the only constraint is not truth, only guarantee database good disorderly, but means that the registration page will display, database without corresponding to insert the data,

Will not have any influence the number of rows returned

If plus the only constraint, you simply remove the judge user name repetition logic, directly from the returned int value to judge the good,

CodePudding user response:

11 references xiekuntarena response:

Quote: references 9 f qq_21873571 response:

Quote: refer to the eighth floor xiekuntarena response:

And the only constraint is not truth, only guarantee database good disorderly, but means that the registration page will display, database without corresponding to insert the data,

Will not have any influence the number of rows returned

If plus the only constraint, you simply remove the judge user name repetition logic, directly from the returned int value to judge well,

+ 1

CodePudding user response:

reference 4 floor Inhibitory response:

database table set the username is unique, repeated injection will insert the failure, only one can insert success

The said accurate, the only constraint, in addition, your code must change, change the following code, there is no need to go to else

If (xxxDao. IsUsernameExist (" love ")) {
Return "the user name you entered has been occupied, please try other user name, thank you!" ;
}
XxxDao. InsertUser (user);
Return "user registration, please login to return to the login page." ;

CodePudding user response:

refer to 7th floor xiekuntarena response:

add a synchronized lock in isUsernameExist method, can achieve 100 individual method but one by one check

personal plus the only constraint is to think database, application level of synchronized lock, or difficult to handle in a distributed environment, if the same user name and the database returns affect records, or corresponding anomaly, the program can according to the prompt the user to make processing the user name has been registered,
As to why we have to be before the database constraints username judgment logic, personal feel, mainly the user experience and performance problems, when we were in the input user name generally this user name will be the background check via ajax, without user information such as the form filled out your submission then told the user that the user name already exists, the form to fill in, and, moreover, in the first level filter illegal information (user name repetition) insert, not after the insert to find unique, to a certain extent, reduce the burden of the database

CodePudding user response:

nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull