1. Basic concepts
Optimistic locking and pessimistic locking are two different approaches to solve the problem of data competition in concurrent scenarios, which will eventually lead to data inconsistency.
- Optimistic locking: take optimistic when operating data, don’t think other threads (coroutines) will modify this part of the data at the same time, so you don’t locked, just judgment when update operation is performed during that time, the data is modified by other threads (coroutines), if the data has been modified, then give up updates, or perform an update.
- Pessimistic locking: Taking a pessimistic attitude when manipulating data, assuming that other threads (coroutines) will modify the data at the same time. Therefore, when manipulating data, the data will be locked, during which no other thread is allowed to modify the data. The lock will not be released until its own operation is completed.
2. Advantages and disadvantages
Optimistic locks and pessimistic locks have their own characteristics, and each has its own scenario where it can be used, and one can’t be considered superior to the other.
- The locking mechanism of pessimistic lock can ensure data security well, and pessimistic lock can be used for changes involving sensitive data. In addition, pessimistic lock is suitable for scenarios with high probability of concurrent conflicts and large number of writes, because optimistic lock frequently conflicts (fails) during the implementation of update operation, which will constantly retry and waste resources. Of course, there are drawbacks. The mechanism of handling locks imposes additional overhead on the database, and there is the possibility of deadlock. Reduced system throughput. If one transaction locks a row, other transactions must wait for the transaction to complete before processing that row. This is not so obvious for scenarios with a lot of reads.
- Optimistic locking itself is unlocked (sometimes in conjunction with locking, but essentially unlocked). It only determines whether the data has been modified during the update, so there is no additional database overhead. Moreover, it does not limit parallelism, so it is more suitable for scenarios with low probability of concurrency conflicts and high number of reads. The disadvantages are also obvious, such as retry when a concurrency conflict occurs, additional overhead, etc.
3. Implementation mode
3.1 pessimistic locks
Pessimistic locking is implemented by locking, either on a block of code or on data (as in MySQL with exclusive locking).
3.2 optimistic locking
Optimistic locking is commonly implemented in two ways: version number mechanism and CAS mechanism.
3.2.1 version number mechanism
The basic idea of implementation:
- Add a version field to represent the version number of the data. Each time the data changes, the corresponding version value is also incremented by 1
- Each time you fetch the data, you also fetch the version value (you can also add a timestamp, etc.)
- When other operations are completed to perform the update, it determines whether the current version is the same as the version read in the previous step. If so, it is considered that the data has not been modified
- If the versions are inconsistent, the update fails, retry and other policies are pursued
The core SQL:
Assume version = 5 at read time
UPDATE table SET status = ? , version = 5+1 WHERE key = ? AND version = 5;Golang Code Practice:
package main import ( "errors" "log" "os" "sync" "time" jefdb "github.com/jefreywo/golibs/db" "gorm.io/gorm" ) func Main () {db, err := jefdb.newmysqldb (&jefdb.mysqldbConfig {User: "root", Password: "12345", Host: "127.0.0.1", Port: 3306, Dbname: "test", MaxIdleConns: 5, MaxOpenConns: 80, LogWriter: os.Stdout, Colorful: true, SlowThreshold: time.Second * 2, LogLevel: "info", }) if err ! = nil { log.Fatalln(err) } var wg sync.WaitGroup wg.Add(2) go func() { defer wg.Done() err := updateUserBalance(db, 56) if err ! = nil { log.Println("updateUserBalance(100):", err) } }() go func() { defer wg.Done() err := updateUserBalance(db, 123) if err ! = nil { log.Println("updateUserBalance(200):", Err)}}() wg.wait ()} var NorowsAffectError = Error. New(" Optimal lock failed to update data ") func UpdateUserBalance (db * gorm.db, Var u jefdb.JUser if err := db. select ("id,balance,version").first (&u, rewardint64) error {var u jefdb. 1).Error; err ! = nil {return err} // return err = 3 var err error for I := 0; i < retry; I++ {err = db.transaction (func(tx * gorm.db)) error {// Result := tx.table ("j_user"). Where("id =? AND version = ?" , u.id, u.version). // Updates(map[string]interface{}{"balance": u.balance + reward, "version": U.Version +1, // version = +1}) if result.Error! = nil {return result.error} if result.rowsaffected == 0 {log.println (" Update failed, reward:",); reward) return NoRowsAffectedError } return nil }) if err == nil { break } else { if err == NoRowsAffectedError { time.Sleep(time.Second) continue } break } } return err }