“This is the 28th day of my participation in the Gwen Challenge in November. Check out the details: The Last Gwen Challenge in 2021.”

preface

Today to bring you is also more practical function, using Java to generate serial number/order number, listing a few common cases in our life:

1. The order number
2. Product id
3. Transaction number
4. Courier number

I use mysql to store data. Here is how to share it with you

The theoretical knowledge

What is a transaction?

Database transaction (referred to as transaction) is a logical unit in the execution of database management system. It consists of a limited sequence of database operations.

Dirty read, unrepeatable read, phantom read

1. Dirty read: if A transaction modifies the data but has not committed it to the database, then B transaction is not up to date on the data. This phenomenon is called dirty read.

2. Non-repeatable read: transaction A reads the same data for many times. At this time, transaction B modifies the data in the middle of the process, resulting in inconsistent results from multiple reads by transaction A.

3. Phantom reading: when transaction A queries the same range twice before and after, the latter query sees the row not seen by the previous query, because transaction B adds A new data before the latter query.

Mysql has four isolation levels

The isolation level ranges from low to high. The higher the isolation level is, the worse the efficiency is. It is the default isolation level of MySQL.

Transaction isolation level	Dirty read	Unrepeatable read	Phantom read
Read uncommitted (read-uncommitted)	is	is	is
Read -committed	no	is	is
Repeatable read	no	no	is
Serializable	no	no	no

1. Read uncommitted: Allows another transaction to read the data before the transaction is committed. Disadvantages: may cause dirty read, unreal read, and unrepeatable read.

2. Non-repeatable read: Problems may occur under concurrent conditions. For example, user A reads the data and then user B reads the data and modifies it.

3. Repeatable Read: When the repeatable read isolation level is used, all rows referenced by the transaction in any way are locked for the duration of the transaction. Disadvantages: unreal reading

Serialization: don’t use mysql’s MVCC mechanism to acquire read locks on every SELECT request and try to acquire write locks on every update. Disadvantages: Least efficient

Two pessimistic locks

Shared lock (S lock): If transaction T holds A shared lock on data A, other transactions can only hold A shared lock on data A, not an exclusive lock. Transactions that are allowed to share locks can only read data, not modify it.

Exclusive lock (X lock) : If transaction T has an exclusive lock on data A, no other transaction can place any kind of lock on data A. Transactions that are granted exclusive locks can both read and modify data.

Java code implementation

private  String createNewBidNumber(a) {
        CODE20201111xxx CODE+ Current year month day + Number (specific length depends on requirements)
        String front="CODE";/ / prefix
        // Current time encoding
        Date date = new Date();
        String bidDate = new SimpleDateFormat("yyyyMMdd").format(date);
        Object bidService=null;// Change to your own business code
        if(bidService ! =null) {// It is found in the data table, indicating that this order is not the first order today
            String bid = bidService.getXXXX(); // Retrieve the ID, which is the business number
            bid = bid.substring(10.13); // Get the last three digits, that is, the auto generated three digits 001
            int num = Integer.valueOf(bid);
            num ++;  // 加1
            if(num<10){
                String bidNum = String.format("%03d", num);//%03d is only three digits
                String code = front+bidDate+bidNum;
                return code;
            }
            else if(num<100){
                String bidNum = String.format("%03d", num);//num<100
                String code = front+bidDate+bidNum;
                return code;
            }
            else {
                String bidNum = String.valueOf(num);
                String code =front+bidDate+bidNum;
                returncode; }}else {
            int number = 1;
            String bidNum = "00" + number;
            String code = front+bidDate+bidNum;
            returncode; }}Copy the code

Thank you

1. Mysql dirty read, unrepeatable read, unreal read
2. MySQL transaction isolation levels in 10 minutes
3. Object level, the difference between unrepeatable and illusory
4. There are five isolation levels: dirty read, unrepeatable read shared lock, pessimistic lock, and transaction
5. MySQL repeatable read
6. Mysql-serializable – Serialization Isolation Level – Serialization instance scenario