Golang of SQL. Some considerations of DB and summary of reading and writing locks

This is a creation in Article, where the information may have evolved or changed.

Recently write a configuration management service for storing PROTOBUF configuration, business logic is not difficult, 2 days to fix, but the following bug many, also stepped on a lot of pits, recorded here.

First, always thought that the SQL module within the Golang is single-link, so at first within each goroutine open a db, and write a connection pool for management. Follow-up discovery is superfluous, write a lot of code in vain. Golang's SQL module comes with the connection pooling function, the connection is allocated when the SQL statement is executed, and is returned to the connection pool after execution, so it is assumed that a program is a DB on the Golang SQL module.

Since there is support for connection pooling, it is also important to keep in mind that connection pooling is not compromised. Assuming that you use query to execute queries, a SQL is returned. The rows structure, which consumes a connection, is closed automatically only after traversing, so it is best to get the Close method of rows after rows, and multiple close is fine.

Then, because the superior denies the idea of using transaction, the transaction can only be controlled within the program. At first, the entire SQL execution model shares a read-write lock, in the case of performance testing, read TPs at around 3k, performance is OK, but write only 200. This is unacceptable, and then carefully analyzed the next code, write in front of the lock, then a few parallel routine will wait for the possession of the lock of the routine write finished before there will be a second routine write operation, is tantamount to white line, and the test case is insert new record just, There is no question of conflict. Now think of a lock although it is easy to write, but the performance impact is very large, so today wrote a new read-write lock manager, binding a specific key, each key in the current key lock is occupied, will return the occupied lock, and will reference the count value of +1, assuming there is no corresponding lock, then return the new lock. When the lock is released, the reference value of the lock for the current key is assumed to be 0, which means that the lock is destroyed if it is not locked by another routine, otherwise the reference count value-1.

In this case, the different SQL generated a key, using this key to write conflict management, when the two SQL has the same key, then the lock competition, assuming that key is not the same, there will be no competition. At the same time, reference counting is used to avoid the leakage of read-write locks, which is good for long-term stable running servers.

Read operation, there is currently no write lock in the case, then read directly, so read performance will not have a great impact.

Under the new lock design, TPS has been upgraded from 200 to 1200, which is an acceptable range. The key point of this scheme is that key generation is to extract the rows affected by each SQL operation, so as long as you can get this key, it is very convenient to generate a read-write lock.