SQLite SharedCache多线程读取 [英] SQLite SharedCache MultiThread Reads

问题描述

我正在Ubuntu虚拟机上开发一个多线程sqlite数据库应用程序，该虚拟机分配了4个处理器.我正在使用sqlite版本3.7.13.我创建了一个测试，以验证可以同时从数据库读取多个线程/连接.

I am developing a multi-threaded sqlite database application on an Ubuntu virtual machine that has 4 processors allocated to it. I am using sqlite version 3.7.13. I created a test to verify that multiple threads/connections can read from the database at the same time.

我有两个可执行文件.第一个可执行文件只是创建一个数据库，在该数据库中创建一个表，在该表中插入50个项目，然后关闭数据库.这根本不涉及任何多线程，而只是为了向数据库提供其中的条目.

I have two executables. The first executable simply creates a database, creates 1 table in that database, inserts 50 items into that table, and then closes the database. This does not involve any multi-threading at all and is simply meant to provide a database with entries in it.

第二个可执行文件创建多个线程以从数据库中读取并等待它们完成，并记录所有线程完成所花费的时间.每个线程执行以下操作: -使用sqlite_open_v2()创建数据库连接，以便每个线程都具有与从第一个可执行文件创建的数据库的单独连接 -在一个数据库表上执行100000 SELECTS(每个选择查询表中的一行) -关闭数据库连接

The second executable creates multiple threads to read from the database and waits for them to finish and records the time that it took for all of the threads to complete. Each thread does the following: -create a database connection using sqlite_open_v2() so that each thread has its own individual connection to the database created from the first executable -perform 100000 SELECTS on the one database table (each select queries for one row in the table) -close the database connection

当我在指定SQLITE_OPEN_READWRITE作为每个线程中sqlite_open_v2的标志的情况下运行此测试时，我得到以下结果，表示执行所有查询的总时间:

When I ran this test with SQLITE_OPEN_READWRITE specified as the flags for sqlite_open_v2 in each thread, I get the following results for total time to perform all queries:

1个线程-0.65秒 2个线程-0.70秒 3个线程-0.76秒 4个线程-0.91秒 5个线程-1.10秒 6个线程-1.28秒 7个线程-1.57秒 8个线程-1.78秒

1 Thread - 0.65 seconds 2 Threads - 0.70 seconds 3 Threads - 0.76 seconds 4 Threads - 0.91 seconds 5 Threads - 1.10 seconds 6 Threads - 1.28 seconds 7 Threads - 1.57 seconds 8 Threads - 1.78 seconds

这些结果是预期的，因为随着我添加线程，时间增加了一点点(可能是由于线程之间的上下文切换和其他原因)，这意味着读取基本上是并行进行的.

These results were as expected as the times increase just a little (probably from context switching between threads and other causes) as I add threads, meaning that reads are basically being done in parallel.

但是，当我使用SQLITE_OPEN_READWRITE运行相同的测试时| SQLITE_OPEN_SHAREDCACHE的标志，我得到以下结果:

However, when I ran this same test with SQLITE_OPEN_READWRITE | SQLITE_OPEN_SHAREDCACHE for the flags, I get the following results:

1个线程-0.67秒 2个线程-2.43秒 3个线程-4.81秒 4个线程-6.60秒 5个线程-8.03秒 6个线程-9.41秒 7个线程-11.17秒 8个线程-12.79秒

1 Thread - 0.67 seconds 2 Threads - 2.43 seconds 3 Threads - 4.81 seconds 4 Threads - 6.60 seconds 5 Threads - 8.03 seconds 6 Threads - 9.41 seconds 7 Threads - 11.17 seconds 8 Threads - 12.79 seconds

从这些结果看来，共享缓存模式下的某些内容阻止了同时在数据库中进行多次读取.我已经验证了确实有不同的线程并行运行(线程4读取，线程8读取，线程2读取等)，而不是线程1执行所有读取，线程2执行所有读取，线程3执行所有读取，等等.).但是，似乎每个事务的读取都是串行进行的，或者其他原因正在减慢共享缓存中的数据库的速度.

From these results, it appears that something in shared cache mode is preventing multiple reads from happening in the database at the same time. I have verified that indeed different the threads are running in parallel (thread 4 reads, thread 8 reads, thread 2 reads, etc. rather than thread 1 performs all its reads, thread 2 performs all its reads, thread 3 performs all its reads, etc.). However, it appears that the reads for each individual transaction are being done in serial, or something else is slowing down the database in shared cache.

为什么在共享缓存模式下添加线程而不是在不添加线程的情况下增加的时间如此多?有没有办法解决此问题并仍然使用共享缓存模式?

Why am I seeing such a high increase in times as I add threads in shared cache mode as opposed to without it? Is there a way to fix this and still use shared cache mode?

感谢您的帮助.非常感谢.

Thanks for any help. It is much appreciated.

推荐答案

目前，我只能说，在shared cache mode中，每个线程对每个请求执行多次其他read mutex lock()(锁定在共享缓存，锁定主表，锁定请求的表).当然，它有一些开销.

Currently, I can only say that in shared cache mode each thread do additional read mutex lock() on each request several times (lock at shared cache, lock master table, lock requested table). Of course, it have some overhead.

为避免这种情况，可以使用PRAGMA read_uncommitted = true;，但是如果另一个数据库连接在读取表时修改了该表，则可能导致查询结果不一致，但这也意味着该连接在read-中打开了一个读取事务.未提交模式既不能阻止也不能被任何其他连接阻止.

To avoid this you can use PRAGMA read_uncommitted = true;, but this can lead to inconsistent query results if another database connection modifies a table while it is being read, but it also means that a read-transaction opened by a connection in read-uncommitted mode can neither block nor be blocked by any other connection.

(您能提供代码吗?)

这篇关于SQLite SharedCache多线程读取的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！