Concurrent problem types

When multiple transactions in the same application or multiple transactions in different applications are executed concurrently on the same dataset, many unexpected problems may occur. These problems can be divided into the following three types: dirty read, non repeatable read, and phantom read.
Dirty read: it is known that there are two transactions a and B. A reads the data that has been updated by B but has not been committed. Then B rolls back the transaction, and the data read by a is modified and committed. At this time, the data read by a is dirty data.
Explain with examples:
Scenario: when the company paid the salary, the leader sent 5000 yuan to Tom's account, but the transaction was not submitted. However, Tom happened to check the account and found that the salary had arrived, and the account was 5000 yuan more. He was very happy, but unfortunately, the leader found that the salary paid to Tom was 2000 yuan, so he quickly rolled back the transaction. After modifying the amount, he submitted the transaction. When Tom checked the account again, he found that the account was only 2000 yuan more, Tom was happy in the air, and from then on, he was depressed and embarked on the road of no return
Analysis: the above situation is dirty reading. There are two concurrent transactions: "transaction B: leaders pay Tom" and "transaction A: Tom queries the salary account". Transaction A reads the uncommitted data of transaction B.
For better understanding, we use code presentation:
First, insert the account table in the database, add data, then execute the main method in the Boss class, and then the main method in the Employee class. After the main method in the Boss class is executed, execute the main method in the Employee class, and finally observe the output of the main method in the Employee class.

create table account(
	id int(36) primary key comment 'Primary key',
  card_id varchar(16) unique comment 'Card No',
  name varchar(8) not null comment 'name',
  balance float(10,2) default 0 comment 'balance'
)engine=innodb;

insert into account (id,card_id,name,balance) values (1,'6226090219290000','Tom',1000);

Boss class

public class Boss {

	public static void main(String[] args) {
		Connection connection = null;
		Statement statement = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://127.0.0.1:3306/test";
			connection = DriverManager.getConnection(url, "root", "root");
			connection.setAutoCommit(false);
			statement = connection.createStatement();
			String sql = "update account set balance=balance+5000 where card_id='6226090219290000'";
			statement.executeUpdate(sql);
			Thread.sleep(30000);//After 30 seconds, I found that the salary was wrongly paid
			connection.rollback();
			sql = "update account set balance=balance+2000 where card_id='6226090219290000'";
			statement.executeUpdate(sql);
			connection.commit();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			//Free resources
		}
	}
}

Employye

public class Employye {

	public static void main(String[] args) {
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://127.0.0.1:3306/test";
			connection = DriverManager.getConnection(url, "root", "root");
			statement = connection.createStatement();
			String sql = "select balance from account where card_id='6226090219290000'";
			resultSet = statement.executeQuery(sql);
			if(resultSet.next()) {
				System.out.println(resultSet.getDouble("balance"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			//Free resources
		}
	}
}

**Non repeatable read 😗* It is known that there are two transactions a and B. A reads the same data multiple times, and B modifies and commits the data during a's multiple reads, resulting in inconsistent results when a reads the same data multiple times.
Give examples to explain non repeatable reading and repeatability:
Scene: Tom takes his salary card to spend money. After he has had enough to eat and drink, he pays the bill at the cashier. The waiter tells him that he will spend 1000 yuan this time. Tom gives his bank card to the waiter. The waiter inserts the bank card into the POS machine. The POS machine reads that the balance of the card is 3000 yuan. When Tom dawdles to enter the password, his wife transfers 3000 yuan of Tom's salary card to his account and submits the transaction, When Tom finished inputting his password and clicked the "confirm" button, the POS machine checked that Tom's salary card had no money, and the deduction failed. Tom was very puzzled. It was clear that the card had money, so he suspected that there was A ghost in the POS, but there was no evidence. The matter was finally settled. "Transaction B: Tom's wife transferred money online". Transaction A read the data in advance, and transaction B then updated the data and submitted the transaction. When transaction A read the data again and deducted, The data has changed.
Analysis: the above conditions are not repeatable
Scenario: when Tom takes his salary card to consume, once the POS machine reads the salary card information (i.e. the transaction starts), even if Tom's wife transfers money, after Tom enters the password and clicks the "confirm" button, the POS machine checks that the balance on Tom's salary card has not changed, and the final deduction is successful.
Analysis: the above situation is repeated reading.
For better explanation, we adopt code presentation:
First, execute the main method in the Machine class, then execute the main method in the life class, and finally observe the output of the main method in the Machine class.
First, create a savings table account in the database and inject basic data.

create table account(
	id int(36) primary key comment 'Primary key',
  card_id varchar(16) unique comment 'Card No',
  name varchar(8) not null comment 'name',
  balance float(10,2) default 0 comment 'balance'
)engine=innodb;

insert into account (id,card_id,name,balance) values (1,'6226090219290000','Tom',3000);
insert into account (id,card_id,name,balance) values (2,'6226090219299999','LilY',0);

Machine class:

public class Machine {

	public static void main(String[] args) {
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		try {
			double sum=1000;//Consumption amount
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://127.0.0.1:3306/test";
			connection = DriverManager.getConnection(url, "root", "root");
			connection.setAutoCommit(false);
			statement = connection.createStatement();
			String sql = "select balance from account where card_id='6226090219290000'";
			resultSet = statement.executeQuery(sql);
			if(resultSet.next()) {
				System.out.println("Balance:"+resultSet.getDouble("balance"));
			}
			
			System.out.println("Please enter the payment password:");
			Thread.sleep(30000);//Password input succeeded after 30 seconds
			
			resultSet = statement.executeQuery(sql);
			if(resultSet.next()) {
				double balance = resultSet.getDouble("balance");
				System.out.println("Balance:"+balance);
				if(balance<sum) {
					System.out.println("Insufficient balance, deduction failed!");
					return;
				}
			}
			
			sql = "update account set balance=balance-"+sum+" where card_id='6226090219290000'";
			statement.executeUpdate(sql);
			connection.commit();
			System.out.println("Deduction succeeded!");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			//Free resources
		}
	}
}

Wife class:

public class Wife {

	public static void main(String[] args) {
		Connection connection = null;
		Statement statement = null;
		try {
			double money=3000;//Transfer amount
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://127.0.0.1:3306/test";
			connection = DriverManager.getConnection(url, "root", "root");
			connection.setAutoCommit(false);
			statement = connection.createStatement();
			String sql = "update account set balance=balance-"+money+" where card_id='6226090219290000'";
			statement.executeUpdate(sql);
			sql = "update account set balance=balance+"+money+" where card_id='6226090219299999'";
			statement.executeUpdate(sql);
			connection.commit();
			System.out.println("Transfer succeeded");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			//Free resources
		}
	}
}

Phantom read: it is known that there are two transactions a and B. A reads data from a table, and then B inserts some new data into the table. As a result, a reads the same table again, and there will be several more rows. In short, a transaction reads records in a range successively, but the number of records read each time is different, which is called phantom read.
Explain with examples:
Scene: Tom's wife works in the banking department. She often checks Tom's salary card consumption records through the bank's internal system. One day in May, 2019, she inquired Tom's total consumption (select sum(amount) from record where card_id= '6226090219290000' and date_format(create_time, '%Y-%m') = '2019-05') is 80 yuan. Tom's wife is very surprised and thinks, "my husband is so frugal. It's good to marry him!", At this time, Tom just bought the bill at the cash register after eating haisai outside and spent 1000 yuan, that is, he added a consumption record of 1000 yuan and submitted the transaction. The wife immersed in happiness inquired Tom's salary card consumption details of the current month (select amount from record where card_id= "6226090219290000" and date_format(create_time,'%Y-%m ") = '2019-05'), and found that there was a consumption of 1000 yuan, Tom's wife was instantly furious. The takeout ordered a large durian. When the evening came, Tom lived in deep water and felt the pain of a needle in his knee
Analysis: the above situation is phantom reading. For two concurrent transactions, "transaction A: obtaining transaction B consumption records" and "transaction B: adding new consumption records", there is one more piece of data when transaction A obtains transaction B consumption records.
To better explain unreal reading, we use code presentation:
First, execute the main method in the Bank class, then execute the main method in the Husband class, and finally observe the output of the main method in the Bank class.
First, create a storage table account to inject data, and then create a consumption table record to inject basic data.

create table account(
	id int(36) primary key comment 'Primary key',
  	card_id varchar(16) unique comment 'Card No',
  	name varchar(8) not null comment 'name',
  	balance float(10,2) default 0 comment 'balance'
)engine=innodb;
insert into account (id,card_id,name,balance) values (1,'6226090219290000','Tom',3000);

create table record(
	id int(36) primary key comment 'Primary key',
    card_id varchar(16) comment 'Card No',
    amount float(10,2) comment 'money',
    create_time date comment 'dissipate'
)engine=innodb;
insert into record (id,card_id,amount,create_time) values (1,'6226090219290000',37,'2019-05-01');
insert into record (id,card_id,amount,create_time) values (2,'6226090219290000',43,'2019-05-07');

Bank class:

public class Bank {

	public static void main(String[] args) {
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://127.0.0.1:3306/test";
			connection = DriverManager.getConnection(url, "root", "root");
			connection.setAutoCommit(false);
			statement = connection.createStatement();
			String sql = "select sum(amount) total from record where card_id='6226090219290000' and date_format(create_time,'%Y-%m')='2019-05'";
			resultSet = statement.executeQuery(sql);
			if(resultSet.next()) {
				System.out.println("Total:"+resultSet.getDouble("total"));
			}

			Thread.sleep(30000);//Query the consumption details of May 2019 in 30 seconds
			
			sql="select amount from record where card_id='6226090219290000' and date_format(create_time,'%Y-%m')='2019-05'";
			resultSet = statement.executeQuery(sql);
			System.out.println("Consumption details:");
			while(resultSet.next()) {
				double amount = resultSet.getDouble("amount");
				System.out.println(amount);
			}
			connection.commit();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			//Free resources
		}
	}
}

Husband class:

public class Husband {

	public static void main(String[] args) {
		Connection connection = null;
		Statement statement = null;
		try {
			double sum=1000;//Consumption amount
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://127.0.0.1:3306/test";
			connection = DriverManager.getConnection(url, "root", "root");
			connection.setAutoCommit(false);
			statement = connection.createStatement();
			String sql = "update account set balance=balance-"+sum+" where card_id='6226090219290000'";
			statement.executeUpdate(sql);
			sql = "insert into record (id,card_id,amount,create_time) values (3,'6226090219290000',"+sum+",'2019-05-19');";
			statement.executeUpdate(sql);
			connection.commit();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			//Free resources
		}
	}
}