Mysql database concurrency, locking problems (shared locks and exclusive locks)

My related blog ：
java Connection in code program mysql Method and code of database
mysql Database concurrent locking problem ,java Code

This is about how java Demonstration of locking database tables in the program

It uses Shared lock and Exclusive lock .
Shared lock ： After locking, all users can view the information , Do not modify , Until the lock is released by the current user
Basic statement ：

select *from table_name lock in share mode;

Exclusive lock ： After the lock , Only the current user can modify or delete this information , Other users can only view , Do not modify .
Basic statement ：

select *from table_name where SNo = "xxx" for update;// Row lock 

select *from table_name where SNo<>1 for update;// Table locks 

I believe the partners who are searching for this problem , Have some database knowledge , Then you know that the database is dirty 、 Deadlock and other problems and principles .

So don't say much , Go straight to the presentation , I hope it helped you .
1. First open the program file ,Navicat premium（ Software , Easy to manage database ）.
java in ： The following code is the application of exclusive lock .

try {
    
                sql.execute("begin ");
                sql.execute("select *from studentinfo where SNo= "+"\""+SNo+"\""+"for update ");
                res = sql.execute("update studentinfo set "+Modify+"="+"\""+content+"\""+"where SNo="+"\""+SNo+"\";");
                sql.execute("commit ;");
                // Why do you commit, Because the execution time of our addition, deletion and modification lock is limited , Immediately after the user adds or deletes this commit that will do , Not yet commit In the time period , Because this user has locked this line of information , Then other users can't modify it ！
                if (!res){
    
                    System.out.println(" The update is successful ");
                }else {
    
                    System.out.println(" Update failed ");
                }
            } catch (SQLException e) {
    
                e.printStackTrace();
            }

First we will java About China SQL commit Comment out the operation of , And execute the program （ Modify student information ）：

as follows , The student's birthday is modified in the console , But there was no commit Operation ！

And then we go into Navicat in , Also for this table studentinfo The student in （1234567890） Carry out information operation , Be careful ： Here is the row lock , Only the student number is locked 1234567890 This line of information , It's not a watch lock . Because there is SNo="1234567890" This student , If not, upgrade to table lock .

Execute this line of UPDATE statement , We can find out , It is waiting , Not implemented ！

We will java Termination of procedure
The update statement is successfully executed , We can find it waiting 15s For a long time ~
That's why , The previous user has always held the exclusive lock of this row of data , After its release , Other users can carry out subsequent operations .

Let's look at the information in the table ,SName The information of was successfully modified ,Birth Not modified , This is because we terminated the program , It failed commit Submit operation , Therefore, it cannot be modified , If it does commit operation , The data can be modified .

If testing , It can also be in Navicat premium in Create two queries , Separately , To test .
Of course , It is the same to open two windows in the command line .
Query one ：

begin;
select *from studentinfo where SNo="1234567890" for update ;// locked 
delete from studentinfo where SNo="1234567890";
//commit// Don't execute commit operation 

Query two ：

UPDATE studentinfo SET SName="aaa" WHERE SNo = "1234567890";

First execute query 1 , When executing query 2 , Wait for query 2 to enter the waiting , Then execute in query 1 commit Operation can complete the demonstration .

Talk is cheap, show me the code! —— Firewood studio ！