Android 线程和数据库锁定
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2647542/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Android threading and database locking
提问by Pandalover
We are using AsyncTasksto access database tables and cursors.
我们AsyncTasks用来访问数据库表和游标。
Unfortunately we are seeing occasional exceptions regarding the database being locked.
不幸的是，我们偶尔会看到有关数据库被锁定的异常。
E/SQLiteOpenHelper(15963): Couldn't open iviewnews.db for writing (will try read-only):
E/SQLiteOpenHelper(15963): android.database.sqlite.SQLiteException: database is locked
E/SQLiteOpenHelper(15963): at android.database.sqlite.SQLiteDatabase.native_setLocale(Native Method)
E/SQLiteOpenHelper(15963): at android.database.sqlite.SQLiteDatabase.setLocale(SQLiteDatabase.java:1637)
E/SQLiteOpenHelper(15963): at android.database.sqlite.SQLiteDatabase.<init>(SQLiteDatabase.java:1587)
E/SQLiteOpenHelper(15963): at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:638)
E/SQLiteOpenHelper(15963): at android.database.sqlite.SQLiteDatabase.openOrCreateDatabase(SQLiteDatabase.java:659)
E/SQLiteOpenHelper(15963): at android.database.sqlite.SQLiteDatabase.openOrCreateDatabase(SQLiteDatabase.java:652)
E/SQLiteOpenHelper(15963): at android.app.ApplicationContext.openOrCreateDatabase(ApplicationContext.java:482)
E/SQLiteOpenHelper(15963): at android.content.ContextWrapper.openOrCreateDatabase(ContextWrapper.java:193)
E/SQLiteOpenHelper(15963): at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:98)
E/SQLiteOpenHelper(15963): at android.database.sqlite.SQLiteOpenHelper.getReadableDatabase(SQLiteOpenHelper.java:158)
E/SQLiteOpenHelper(15963): at com.iview.android.widget.IViewNewsTopStoryWidget.initData(IViewNewsTopStoryWidget.java:73)
E/SQLiteOpenHelper(15963): at com.iview.android.widget.IViewNewsTopStoryWidget.updateNewsWidgets(IViewNewsTopStoryWidget.java:121)
E/SQLiteOpenHelper(15963): at com.iview.android.async.GetNewsTask.doInBackground(GetNewsTask.java:338)
E/SQLiteOpenHelper(15963): at com.iview.android.async.GetNewsTask.doInBackground(GetNewsTask.java:1)
E/SQLiteOpenHelper(15963): at android.os.AsyncTask.call(AsyncTask.java:185)
E/SQLiteOpenHelper(15963): at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:256)
E/SQLiteOpenHelper(15963): at java.util.concurrent.FutureTask.run(FutureTask.java:122)
E/SQLiteOpenHelper(15963): at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:648)
E/SQLiteOpenHelper(15963): at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:673)
E/SQLiteOpenHelper(15963): at java.lang.Thread.run(Thread.java:1060)
Does anybody have a general example for code which writes to a database from a different thread than the one reading and how can we ensure thread safety.
有没有人有一个从不同于读取的线程写入数据库的代码的一般示例，以及我们如何确保线程安全。
One suggestion I've had is to use a ContentProvider, as this would handle the access of the database from multiple threads. I am going to look at this, but is this the recommended method of handling such a problem? It seems rather heavyweight considering we're talking about in front or behind.
我的一个建议是使用 a ContentProvider，因为这将处理来自多个线程的数据库访问。我要看看这个，但这是处理此类问题的推荐方法吗？考虑到我们在前面或后面谈论，它似乎相当重量级。
采纳答案by ubzack
I solved this same exception just by making sure all my database opens have closes, and (more importantly) to assure this, making the scope of each database instance local ONLY to the method that needs it. ContentProvider is a good, safe class to use when accessing a db from multiple threads, but also make sure you're using good db practices:
我只是通过确保我的所有数据库打开都关闭来解决这个相同的异常，并且（更重要的是）为了确保这一点，使每个数据库实例的范围仅适用于需要它的方法。ContentProvider 是一个很好的安全类，可以在从多个线程访问数据库时使用，但也要确保您使用了良好的数据库实践：
- Keep db instances local (no SQLiteDatabase class members!)
- call
close()on the db in the same method in which it's opened - call
close()on the cursors you get from the db - listen to LogCat for any complaints that SQLiteDatabse might have
- 将数据库实例保持在本地（没有 SQLiteDatabase 类成员！）
close()以打开数据库的相同方法调用数据库- 调用
close()您从数据库获得的游标 - 听 LogCat 了解 SQLiteDatabse 可能有的任何投诉
回答by Pandalover
We used a ContentProviderin the end. This appeared to clear up the problems.
我们ContentProvider最终使用了 a 。这似乎解决了问题。
回答by Italo Borssatto
Before some code, let's resume some of the approachs:
在一些代码之前，让我们恢复一些方法：
Semaphores: by far the best solution presented. It goes in the heart of the problem: resource sharing! It will treat the locking of the database access, avoiding conflicts (
database is locked).Java synchronization: A kind of semaphore implementation, but less sofisticated. Using
synchronizedyou will not easily solve some cases involving transactions.ContentProvider: implement
ContentProvidersolve the problem only for some cases (or sweep the problem under the carpet). You'll yet face the same issues. The difference is thatContentProviderpattern will guide you to not make some commom mistakes when accessing Sqlite database. The ContentProvider docssays: "You don't need a provider to use an SQLite database if the use is entirely within your own application."Almost mandatory: keep db instances local, call
close()on the db in the same method in which it's opened usingfinallystatements,close()on the cursors usingfinallystatements, etc are almostmandatory to avoid problems using Sqlite.
信号量：迄今为止最好的解决方案。它是问题的核心：资源共享！它将处理数据库访问的锁定，避免冲突（
database is locked）。Java 同步：一种信号量实现，但不太复杂。使用
synchronized你不会轻易解决一些涉及交易的情况。ContentProvider：
ContentProvider仅针对某些情况实施解决问题（或将问题扫到地毯下）。你仍然会面临同样的问题。不同之处在于ContentProvider模式将指导您在访问 Sqlite 数据库时不要犯一些常见的错误。该ContentProvider的文档说：“你不需要供应商如果使用完全是你自己的应用程序中使用SQLite数据库。”几乎是强制性的：将数据库实例保持
close()在本地，以使用finally语句打开数据库的相同方法调用数据库，使用语句close()在游标上调用finally等几乎是强制性的，以避免使用 Sqlite 出现问题。
Let's show an example of the semaphore solution presented by Moss, which I took from CL.and improoved to cover transactions.
让我们展示一个Moss提出的信号量解决方案的例子，它是我从CL 中获取的。并改进以涵盖交易。
class DataAccess {
private final ReentrantReadWriteLock rwl = new ReentrantReadWriteLock();
private final Lock r = rwl.readLock();
private final Lock w = rwl.writeLock();
public Data readSomething(int id) {
Cursor c = null;
r.lock();
try {
c = getReadableDatabase().query(...);
return c.getString(0);
} finally {
if (c != null) c.close();
r.unlock();
}
}
public void changeSomething(int id, int value) {
w.lock();
try {
getWritableDatabase().update(...);
} finally {
w.unlock();
}
}
private void beginTransactionWithSemaphores() {
getWritableDatabase().beginTransactionWithListener(new SQLiteTransactionListener() {
@Override
public void onBegin() {
w.lock();
}
@Override
public void onRollback() {
w.unlock();
}
@Override
public void onCommit() {
w.unlock();
}
});
}
}
回答by Moss
Take into account that SQLite databases are file based and are not intended to be able to be accessed in a multi-process way. The best procedure on mixing SQLite with multi-processing is using semaphores (aquire(), release()) in each database related access.
考虑到 SQLite 数据库是基于文件的，不能以多进程方式访问。将 SQLite 与多处理混合的最佳过程是在每个与数据库相关的访问中使用信号量（aquire()、release()）。
If you create a Db wrapper that aquires/releases a global semaphore your DB access will be thread safe. Indeed this means that you could get a bootleneck because you are queueing the access to the DB. So in addition you could only wrap the access using semaphores if it's an operation that alters the database, so while you are alterin the db no one will be able to access it and wait until the write process has been completed.
如果您创建一个获取/释放全局信号量的 Db 包装器，您的数据库访问将是线程安全的。实际上，这意味着您可能会获得引导，因为您正在排队访问数据库。因此，此外，如果是更改数据库的操作，则只能使用信号量来包装访问，因此当您更改数据库时，没有人能够访问它并等待写入过程完成。
回答by Ashish Saini
We could not share Db connection with multiple thread to perform read and write operation in database simultaniously.We will have to make single object of DB using syncronization concept and we will perform one task at a time .We will use singleton pattern to make the DB object and it will be share within multiple threads.At a time will perform single task . then we will start other task or any operation on DB . Content provider is not the solution of DB locking issue .
我们无法与多个线程共享 Db 连接以同时在数据库中执行读写操作。我们将不得不使用同步概念制作 DB 的单个对象，我们将一次执行一项任务。我们将使用单例模式来制作 DB对象，它将在多个线程中共享。一次将执行单个任务。然后我们将在 DB 上启动其他任务或任何操作。内容提供者不是数据库锁定问题的解决方案。
import java.util.concurrent.atomic.AtomicInteger;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
public class DatabaseManager {
private AtomicInteger mOpenCounter = new AtomicInteger();
private static DatabaseManager instance;
private static SQLiteOpenHelper mDatabaseHelper;
private SQLiteDatabase mDatabase;
//private static String DB_PATH = "";
// private static String DB_NAME = "xyz.db";// Database name
private static String dbPathh;
public static synchronized void initializeInstance(SQLiteOpenHelper helper,
String dbPath) {
if (instance == null) {
instance = new DatabaseManager();
mDatabaseHelper = helper;
dbPathh=dbPath;
}
}
public static synchronized DatabaseManager getInstance() {
if (instance == null) {
throw new IllegalStateException(DatabaseManager.class.getSimpleName() +
" is not initialized, call initializeInstance(..) method first.");
}
return instance;
}
public synchronized SQLiteDatabase openDatabase(String thread) {
if(mOpenCounter.get() == 0) {
// Opening new database
// mDatabase = mDatabaseHelper.getWritableDatabase();
MyLog.e("Path Of DataBase", dbPathh);
// mDatabase=mDatabaseHelper.getWritableDatabase();
mOpenCounter.incrementAndGet();
mDatabase=SQLiteDatabase.openDatabase(dbPathh, null,
SQLiteDatabase. CREATE_IF_NECESSARY|SQLiteDatabase.OPEN_READWRITE);
MyLog.e("Open Data Base", " New Connection created" +thread);
}
else{
MyLog.e("Open Data Base", " Old Connection given " +thread);
}
// Toast.makeText(NNacres.getConfig(), "open conn: present connection =
" +mOpenCounter.get(), Toast.LENGTH_LONG).show();
return mDatabase;
}
public synchronized void closeDatabase() {
MyLog.e("Close db connection", ""+mOpenCounter.get());
if(mOpenCounter.get() == 1) {
// Closing database
mDatabase.close();
mOpenCounter.decrementAndGet();
Log.e("DB CLOSED", "DONE");
}
//Toast.makeText(NNacres.getConfig(), "close conn: after close =
" +mOpenCounter.get(), Toast.LENGTH_LONG).show();
}
}
and write this method in your YourSQLiteDataABse helper class which extends SQLiteOpenHelper Class
并在扩展 SQLiteOpenHelper 类的 YourSQLiteDataABse 助手类中编写此方法
public SQLiteDatabase getWritableDatabase() {
DatabaseManager.initializeInstance(this,"data/data/your packgae name/databases/xyz");
return DatabaseManager.getInstance().openDatabase(getClass().getSimpleName());
}
public static String getMyDbPath(String DB_NAME, Context context) {
String myDbPath = context.getDatabasePath(DB_NAME).getPath();
MyLog.e("DB Path: "+myDbPath);
return myDbPath;
}
回答by user868114
You must be calling getWritableDatabase()from a function rather then the constructor of the db helper class. If the db helper class object is created with SQLiteDatabase.openOrCreateDatabase(DB_PATH, null);or similar and then getWritableDatabase()is called from a function, it will try to make a synchronous call to DB causing a DB lock exception.
您必须getWritableDatabase()从函数调用，而不是从 db helper 类的构造函数调用。如果 db helper 类对象是用SQLiteDatabase.openOrCreateDatabase(DB_PATH, null);或 类似的方法创建的， 然后getWritableDatabase()从函数中调用，它将尝试对 DB 进行同步调用，从而导致 DB 锁异常。
回答by Erwin Smout
Are you talking of a single user action that, inside your program, causes multiple threads to be run, more than one of which may be accessing the database in update mode ?
您是在谈论在您的程序中导致多个线程运行的单个用户操作，其中多个线程可能正在以更新模式访问数据库吗？
That's bad design, period. There is no way for you to know in which order the threads will be scheduled by your OS (/VM), and therefore there is no way for you to know in which order the database accesses will happen, and that is very likely to imply that there is no way for you to know that database accesses will always happen in the order that you are expecting.
那是糟糕的设计，句号。您无法知道您的操作系统 (/VM) 将按哪个顺序调度线程，因此您无法知道数据库访问将按哪个顺序发生，这很可能意味着您无法知道数据库访问将始终按照您期望的顺序进行。
All database accesses generated by/coming from some user action should all be done in one single thread.
由某个用户操作生成/来自某个用户操作的所有数据库访问都应该在一个线程中完成。
