原理:文件数据库sqlite,同一时刻允许多个进程/线程读,但同一时刻只允许一个线程写。在操行写操作时,数据库文件被琐定,此时任何其他读/写操作都被阻塞,如果阻塞超过5秒钟(默认是5秒,能过重新编译sqlite可以修改超时时间),就报"database is locked"错误。
所以,在操作sqlite时,应该即时关闭连接;打开连接后,尽量减少非常费时的操作。
1. 方法
在页面中用到了ViewPager控件,ViewPager中的内容分别是两个ListView,两个ListView的数据都来自本地数据库(先从网络下载数据,然后更新本地数据库),在实际的使用过程中发现会出现SQLiteDatabaseLockedException: database is locked的问题。
经网上搜索资料,发现是读写数据库时存在的同步问题,所以采用单例+同步锁的方法,并且在每次数据库操作后都关闭数据库,经测试后发现没有在出现上述问题。
以下是两个主类
DBHelper.java(这个类用来管理数据库)
- public class DBHelper extends SQLiteOpenHelper {
- private final String TAG = this.getClass().getSimpleName();
- public final static String DATABASE_NAME = "test.db";
- public final static String TABLE = "table";
- public final static int DATABASE_VERSION = 2;
- public DBHelper(Context context) {
- super(context, DATABASE_NAME, null, DATABASE_VERSION);
- }
- private static DBHelper mInstance;
- public synchronized static DBHelper getInstance(Context context) {
- if (mInstance == null) {
- mInstance = new DBHelper(context);
- }
- return mInstance;
- };
- @Override
- public void onCreate(SQLiteDatabase db) {
- try {
- db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE
- + "(id INTEGER PRIMARY KEY ,data BLOB)");
- } catch (SQLiteException e) {
- e.printStackTrace();
- }
- }
- @Override
- public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
- // 删除原来的数据表
- db.execSQL("DROP TABLE IF EXISTS " + TABLE);
- // 重新创建
- onCreate(db);
- }
- public static byte[] objectToBytes(Object obj) throws Exception {
- ByteArrayOutputStream out = new ByteArrayOutputStream();
- ObjectOutputStream sOut = new ObjectOutputStream(out);
- sOut.writeObject(obj);
- sOut.flush();
- byte[] bytes = out.toByteArray();
- return bytes;
- }
- public static Object bytesToObject(byte[] bytes) throws Exception {
- ByteArrayInputStream in = new ByteArrayInputStream(bytes);
- ObjectInputStream sIn = new ObjectInputStream(in);
- return sIn.readObject();
- }
- }
DBStudentManager类(这里可以定义自己的管理类)
- public class DBStudentManager {
- private DBHelper helper;
- private SQLiteDatabase db;
- public DBStudentManager(Context context) {
- helper = DBHelper.getInstance(context);
- db = helper.getWritableDatabase();
- }
- // 插入
- private void insert(Student student) {
- synchronized (helper) {
- // 看数据库是否关闭
- if (!db.isOpen()) {
- db = helper.getWritableDatabase();
- }
- // 开始事务
- db.beginTransaction();
- try {
- db.execSQL(
- "INSERT INTO " + DBHelper.TABLE + " VALUES(?,?)",
- new Object[] { student.mID,
- DBHelper.objectToBytes(student) });
- db.setTransactionSuccessful(); // 设置事务成功完成
- } catch (SQLException e) {
- e.printStackTrace();
- } catch (Exception e) {
- e.printStackTrace();
- } finally {
- db.endTransaction();
- db.close();
- }
- }
- }
- // 更新
- private void update(Student student) {
- synchronized (helper) {
- if (!db.isOpen()) {
- db = helper.getWritableDatabase();
- }
- db.beginTransaction();
- try {
- db.execSQL("UPDATE " + DBHelper.TABLE
- + "SET data = ? WHERE id = ?",
- new Object[] { DBHelper.objectToBytes(student),
- student.mID });
- db.setTransactionSuccessful(); // 设置事务成功完成
- } catch (SQLException e) {
- e.printStackTrace();
- } catch (Exception e) {
- e.printStackTrace();
- } finally {
- db.endTransaction();
- db.close();
- }
- }
- }
- // 同步
- public void synchronous(List<Student> students) {
- if (students == null) {
- return;
- }
- for (Student student : students) {
- if (query(student.mID) == null) {
- insert(student);
- } else {
- update(student);
- }
- }
- }
- // 删除指定数据
- public void delete(String id) {
- synchronized (helper) {
- if (!db.isOpen()) {
- db = helper.getWritableDatabase();
- }
- db.beginTransaction();
- try {
- db.execSQL("DELETE FROM " + DBHelper.TABLE + " WHERE id = ? ",
- new String[] { id });
- db.setTransactionSuccessful();
- } catch (Exception e) {
- e.printStackTrace();
- } finally {
- db.endTransaction();
- db.close();
- }
- }
- }
- // 删除所有数据
- public void delete() {
- synchronized (helper) {
- if (!db.isOpen()) {
- db = helper.getWritableDatabase();
- }
- db.beginTransaction();
- try {
- db.execSQL("DELETE * FROM " + DBHelper.TABLE);
- db.setTransactionSuccessful();
- } catch (Exception e) {
- e.printStackTrace();
- } finally {
- db.endTransaction();
- db.close();
- }
- }
- }
- // 查找所有的Students
- public List<Student> query() {
- List<Student> students = new ArrayList<Student>();
- synchronized (helper) {
- if (!db.isOpen()) {
- db = helper.getWritableDatabase();
- }
- Cursor c = queryTheCursor();
- Student student = null;
- try {
- while (c.moveToNext()) {
- byte[] bytes = c.getBlob((c.getColumnIndex("data")));
- student = (Student) DBHelper.bytesToObject(bytes);
- students.add(student);
- }
- } catch (Exception e) {
- e.printStackTrace();
- } finally {
- c.close();
- }
- }
- return students;
- }
- // 查找指定ID的Student
- public Student query(String id) {
- Student student = null;
- synchronized (helper) {
- if (!db.isOpen()) {
- helper.getWritableDatabase();
- }
- Cursor c = queryTheCursor(id);
- try {
- while (c.moveToNext()) {
- byte[] bytes = c.getBlob((c.getColumnIndex("data")));
- student = (Student) DBHelper.bytesToObject(bytes);
- break;
- }
- } catch (Exception e) {
- e.printStackTrace();
- } finally {
- c.close();
- }
- }
- return student;
- }
- // 获取游标
- public Cursor queryTheCursor(String id) {
- Cursor c = db.rawQuery("SELECT FROM " + DBHelper.TABLE
- + " WHERE id = ?", new String[] { id });
- return c;
- }
- // 获取游标
- public Cursor queryTheCursor() {
- Cursor c = db.rawQuery("SELECT * FROM " + DBHelper.TABLE);
- return c;
- }
- class Student {
- String mID;
- String mName;
- int mAge;
- }
- }
2. 方法
遇到这个问题,有几种可能,我在下面详细的列出来,方便大家在遇到的时候查看
· 多线程访问造成的数据库锁定。
使用synchronized 关键字来修饰获取数据库连接的方法,或者使用 isDbLockedByOtherThreads方法判断数据库是否被锁住了,然后等待一定的时间再进行访问。public static synchronized DBConnection getConnection(String connectionName) throws Exception { String pathFile = getPath() + connectionName;// 转换目录data下 return new DBConnection(SQLiteDatabase.openDatabase(pathFile, null, SQLiteDatabase.OPEN_READWRITE)); }
3. 方法
如果多线程同时读写(这里的指不同的线程用使用的是不同的Helper实例),后面的就会遇到android.database.sqlite.SQLiteException: database is locked这样的异常。
对于这样的问题,解决的办法就是keep single sqlite connection,保持单个SqliteOpenHelper实例,同时对所有数据库操作的方法添加synchronized关键字。
完美解决sqlite的 database locked 或者是 error 5: database locked 问题
转自:1. http://blog.csdn.net/sdsxleon/article/details/18259973
2.http://blog.csdn.net/lizzy115/article/details/8016066
3.http://www.eoeandroid.com/forum.php?mod=viewthread&tid=333473
4.http://blog.csdn.net/tianyitianyi1/article/details/39453359