分类： SQL

sqlite3 database is locked解决方案

注意：

sqlite3只支持一写多读.
读与读可以同时进行
读与写不可同时进行
写与写不可同时进行

什么时候会返回SQLITE_BUSY错误码？
官方文档给出的解释是：

The SQLITE_BUSY result code indicates that the database file could not be written (or in some cases read) because of concurrent activity by some other database
connection, usually a database connection in a separate process.

For example, if process A is in the middle of a large write transaction and at the same time process B attempts to start a new write transaction, process B will get back an SQLITE_BUSY result because SQLite only supports one writer at a time. Process B will need to wait for process A to finish its transaction before starting a new transaction. The sqlite3_busy_timeout() and sqlite3_busy_handler() interfaces and the busy_timeout pragma are available to process B to help it deal with SQLITE_BUSY errors.

SQLite只支持库级锁，库级锁意味着什么？——意味着同时只能允许一个写操作，也就是说，即事务T1在A表插入一条数据，事务T2在B表中插入一条数据，这两个操作不能同时进行，即使你的机器有100个CPU，也无法同时进行，而只能顺序进行。表级都不能并行，更别说元组级了——这就是库级锁。但是，SQLite尽量延迟申请X锁，直到数据块真正写盘时才申请X锁，这是非常巧妙而有效的。

简单的办法，全局加锁，单线程执行，复杂一点，则可以启用一个专门的数据库线程异步执行操作。

Many concurrent writers? → choose client/server

If many threads and/or processes need to write the database at the same instant (and they cannot queue up and take turns) then it is best to select a database engine that supports that capability, which always means a client/server database engine.

SQLite only supports one writer at a time per database file. But in most cases, a write transaction only takes milliseconds and so multiple writers can simply take turns. SQLite will handle more write concurrency than many people suspect. Nevertheless, client/server database systems, because they have a long-running server process at hand to coordinate access, can usually handle far more write concurrency than SQLite ever will.

mybaties连接sqlite，并读取blob类型数据时，报错 java.sql.SQLFeatureNotSupportedException

java.sql.SQLException: not implemented by SQLite JDBC driver

错误,或者如下错误：

2021-06-03 09:25:20.435 ERROR 15643 --- [nio-8080-exec-7] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: org.apache.ibatis.executor.result.ResultMapException: Error attempting to get column 'appIcon' from result set.  Cause: java.sql.SQLFeatureNotSupportedException
### The error may exist in xxxx.java (best guess)
### The error may involve xxx.select
### The error occurred while handling results
### SQL: SELECT * FROM xxx WHERE id=?
### Cause: org.apache.ibatis.executor.result.ResultMapException: Error attempting to get column 'appIcon' from result set.  Cause: java.sql.SQLFeatureNotSupportedException] with root cause

场景：
具体需求，要求像springboot连接mysql或者pgsql数据库一样，在application配置文件中配置sqlite数据库信息，并实现对blob类型数据（我们的库中有该类型的数据）的读写，按照平常一样写好controller、service、dao、mapper.xml后，执行查询操作后报错

原因分析：
sqlite的driver中，JDBC4ResultSet没有实现以下接口：

public Blob getBlob(int col)
throws SQLException { throw unused(); }
public Blob getBlob(String col)
throws SQLException { throw unused(); }

而是直接抛出了异常。
解决方法：
mapper.xml中该字段的设置：

<select id="queryByList" resultMap="queryBaseResultMap">
    select * from my_blob 
</select>
<resultMap id="queryBaseResultMap" type="com.wx.model.MyBlob" >
    <id column="Id" property="id" jdbcType="INTEGER" />
    <result column="blob" property="blob" typeHandler="com.wx.handler.BlobTypeHandler"/> 
</resultMap>

即blob字段加个typeHandler属性。
然后自定义一个BlobTypeHandler处理类：

public class BlobTypeHandler extends BaseTypeHandler<byte[]> {

    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, byte[] parameter, JdbcType jdbcType) throws SQLException {
        ps.setBytes(i, parameter);
    }

    @Override
    public byte[] getNullableResult(ResultSet rs, String columnName) throws SQLException {
        return rs.getBytes(columnName);
    }

    @Override
    public byte[] getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        return rs.getBytes(columnIndex);
    }

    @Override
    public byte[] getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        return cs.getBytes(columnIndex);
    }
}

注意：实体类中blob字段类型是byte[]。

参考链接

mybaties连接sqlite，并读取blob类型数据时，报 java.sql.SQLException: not implemented by SQLite JDBC driver错误

SQL中MAX函数与Group By获取同一字段重复的数据最大的一条

SQL中MAX函数与Group By一起使用，获取同一字段重复的数据只取记录最大的一条，如下：

mysql> select * from test;
+----+-------+------+-------+
| id | name  | age  |  clz  |
+----+-------+------+-------+
|  1 | qiu   |   22 |     1 | 
|  2 | liu   |   42 |     1 | 
|  4 | zheng |   20 |     2 | 
|  3 | qian  |   20 |     2 | 
|  0 | wang  |   11 |     3 | 
|  6 | li    |   33 |     3 | 
+----+-------+------+-------+
6 rows in set (0.00 sec)

如果想找到每个clz里面的最大的age，则需要使用Group By和Max。

如下的SQL语句，则输出结果有错误：

mysql> select id,name,max(age),clz from test group by clz;
+----+-------+----------+-------+
| id | name  | max(age) |  clz  |
+----+-------+----------+-------+
|  1 | qiu   |       42 |     1 | 
|  4 | zheng |       20 |     2 | 
|  0 | wang  |       33 |     3 | 
+----+-------+----------+-------+
3 rows in set (0.00 sec)

虽然找到的age是最大的age，但是与之匹配的用户信息却不是真实的信息，而是Group By分组后的第一条记录的基本信息。

如果我使用以下的语句进行查找，则可以返回真实的结果。

mysql> select * from (
    -> select * from test order by age desc) as b
    -> group by clz;
+----+-------+------+-------+
| id | name  | age  |  clz  |
+----+-------+------+-------+
|  2 | liu   |   42 |     1 | 
|  4 | zheng |   20 |     2 | 
|  6 | li    |   33 |     3 | 
+----+-------+------+-------+
3 rows in set (0.00 sec)

参考链接