Concurrency issue with two SQLite connections in two different Threads












0















I am using System.Data.SQLite with C#



I have



Thread 1 (UI) - writes to table1
Thread 2 (worker) writes to table1



So I have two threads writing concurrently to same database. I write my data on batches and each batch has a transaction. Batches are needed to avoid locking the database for too long so the other thread can have write access to the database.



But that doesn't work. I hoped thread1 to be able to write to the database between the transaction-batches of thread2 but that doesn't happen unless I have Thread.Sleep(100) between batches. Note having a small value for Thread.Sleep(10) doesn't work either. I understand this is related to Thread context switching but I don't understand why small amounts of Thread.Sleep doesn't do the job.



Is there way to control the priority of who acquire the lock the database, because using Thread.Sleep is bad?



P.S. It seems this is a problem even without a transactions. If I have a loop with many insert statements, the other thread cannot execute anything between insert statements if there is no Thread.Sleep










share|improve this question

























  • 10 milliseconds might be less than the system clock tick meaning it likely doesn't sleep at all. The documentation for Thread.Sleep() suggests that a particular value might do what you want, though...

    – Shawn
    Nov 28 '18 at 14:03
















0















I am using System.Data.SQLite with C#



I have



Thread 1 (UI) - writes to table1
Thread 2 (worker) writes to table1



So I have two threads writing concurrently to same database. I write my data on batches and each batch has a transaction. Batches are needed to avoid locking the database for too long so the other thread can have write access to the database.



But that doesn't work. I hoped thread1 to be able to write to the database between the transaction-batches of thread2 but that doesn't happen unless I have Thread.Sleep(100) between batches. Note having a small value for Thread.Sleep(10) doesn't work either. I understand this is related to Thread context switching but I don't understand why small amounts of Thread.Sleep doesn't do the job.



Is there way to control the priority of who acquire the lock the database, because using Thread.Sleep is bad?



P.S. It seems this is a problem even without a transactions. If I have a loop with many insert statements, the other thread cannot execute anything between insert statements if there is no Thread.Sleep










share|improve this question

























  • 10 milliseconds might be less than the system clock tick meaning it likely doesn't sleep at all. The documentation for Thread.Sleep() suggests that a particular value might do what you want, though...

    – Shawn
    Nov 28 '18 at 14:03














0












0








0








I am using System.Data.SQLite with C#



I have



Thread 1 (UI) - writes to table1
Thread 2 (worker) writes to table1



So I have two threads writing concurrently to same database. I write my data on batches and each batch has a transaction. Batches are needed to avoid locking the database for too long so the other thread can have write access to the database.



But that doesn't work. I hoped thread1 to be able to write to the database between the transaction-batches of thread2 but that doesn't happen unless I have Thread.Sleep(100) between batches. Note having a small value for Thread.Sleep(10) doesn't work either. I understand this is related to Thread context switching but I don't understand why small amounts of Thread.Sleep doesn't do the job.



Is there way to control the priority of who acquire the lock the database, because using Thread.Sleep is bad?



P.S. It seems this is a problem even without a transactions. If I have a loop with many insert statements, the other thread cannot execute anything between insert statements if there is no Thread.Sleep










share|improve this question
















I am using System.Data.SQLite with C#



I have



Thread 1 (UI) - writes to table1
Thread 2 (worker) writes to table1



So I have two threads writing concurrently to same database. I write my data on batches and each batch has a transaction. Batches are needed to avoid locking the database for too long so the other thread can have write access to the database.



But that doesn't work. I hoped thread1 to be able to write to the database between the transaction-batches of thread2 but that doesn't happen unless I have Thread.Sleep(100) between batches. Note having a small value for Thread.Sleep(10) doesn't work either. I understand this is related to Thread context switching but I don't understand why small amounts of Thread.Sleep doesn't do the job.



Is there way to control the priority of who acquire the lock the database, because using Thread.Sleep is bad?



P.S. It seems this is a problem even without a transactions. If I have a loop with many insert statements, the other thread cannot execute anything between insert statements if there is no Thread.Sleep







sqlite sqlite3 system.data.sqlite






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 28 '18 at 13:31







ekalchev

















asked Nov 28 '18 at 13:16









ekalchevekalchev

109111




109111













  • 10 milliseconds might be less than the system clock tick meaning it likely doesn't sleep at all. The documentation for Thread.Sleep() suggests that a particular value might do what you want, though...

    – Shawn
    Nov 28 '18 at 14:03



















  • 10 milliseconds might be less than the system clock tick meaning it likely doesn't sleep at all. The documentation for Thread.Sleep() suggests that a particular value might do what you want, though...

    – Shawn
    Nov 28 '18 at 14:03

















10 milliseconds might be less than the system clock tick meaning it likely doesn't sleep at all. The documentation for Thread.Sleep() suggests that a particular value might do what you want, though...

– Shawn
Nov 28 '18 at 14:03





10 milliseconds might be less than the system clock tick meaning it likely doesn't sleep at all. The documentation for Thread.Sleep() suggests that a particular value might do what you want, though...

– Shawn
Nov 28 '18 at 14:03












2 Answers
2






active

oldest

votes


















1














I don't think SQlite supports true concurrent write transactions... I've used "non-exclusive" transactions on Android and they're documented (I'm writing in my own words from memory) as allowing concurrent reads while there may be a write going on.



Now more to the point... Looking at SQLite transaction docs:



https://sqlite.org/lang_transaction.html




Thus with a deferred transaction, the BEGIN statement itself does
nothing to the filesystem. Locks are not acquired until the first read
or write operation. The first read operation against a database
creates a SHARED lock and the first write operation creates a RESERVED
lock. Because the acquisition of locks is deferred until they are
needed, it is possible that another thread or process could create a
separate transaction and write to the database after the BEGIN on the
current thread has executed. If the transaction is immediate, then
RESERVED locks are acquired on all databases as soon as the BEGIN
command is executed, without waiting for the database to be used.




( emphasis - mine )



OK, so now we've learned that writing to a database requires a RESERVED lock.



Let's see what that is here:



https://sqlite.org/lockingv3.html#reserved_lock




A RESERVED lock means that the process is planning on writing to the
database file at some point in the future but that it is currently
just reading from the file. Only a single RESERVED lock may be active
at one time, though multiple SHARED locks can coexist with a single
RESERVED lock. RESERVED differs from PENDING in that new SHARED locks
can be acquired while there is a RESERVED lock.




OK, so this confirms that SQLite requires a RESERVED lock for writing to the database, and also tells us that only a single RESERVED lock may exist at a time -> only one transaction is allowed to have write access, others will wait.



Now if you're trying to interleave writing transactions from your two threads, each thread executing multiple (granular) transactions - then here is an idea:




  • Replacing Thread.Sleep with Thread.Yield


https://docs.microsoft.com/en-us/dotnet/api/system.threading.thread.yield



This may help with the issue of "the current writing thread's Sleep didn't cause a context switch to the thread we want".



Even with Yield there are still no guarantees that the OS / runtime will switch to the thread you want, but... perhaps worth a try, and at least you won't be artificially making your code run slower.




  • Given what we know about SQLite's "only one transaction is allowed to write", I'd consider the following pattern:


1 - Make a new thread whose job is to process database writes



2 - Queue up write operations to this thread from your current two threads



3 - Have the "operations" be self-contained / sufficient objects, containingf all the data they intend to write



4 - Finally, use a callback with a latch (in C# it's CountDownEvent I believe) to know when an operation is done, so your current threads can await completion



Then you'd only have one writing thread (as far as SQlite is concerned) and still have concurrency between your two current threads.



Pseudo code:



// Write thread

while (item = blockingQueue.getNextItemToWrite()) {
item.executeWrite(database)
item.signalCompletion()
}

// Thread 1

item = new WriteItem(some data that needs to be written)
WriteThread.enqeue(item)
item.awaitCompletion()

// Thread 2 - same as Thread 1


Where WriteItem base class has a CountDownEvent which is 1) awaited on by awaitCompletion and 2) signaled by signalCompletion.



I'm sure there is a way to wrap this into more elegant helper classes and maybe use async / await.






share|improve this answer


























  • Thank you for your answer, I already tried Thread.Yield but it doesn't help. About having a producer-consumer queue for writing to database. One of the reasons I didn't go that way was RESERVED locks. You can't know for sure if a query is going to need to write to the database until it actually execute. This way you can end up having db operations that will never need exclusive lock but will wait for other write operations in the queue.

    – ekalchev
    Nov 30 '18 at 18:01













  • But you don't have to use the "separate write thread" pattern everywhere, only where you need it...

    – Kostya Vasilyev
    Dec 1 '18 at 16:39



















0














Take a look at busy_timeout. In the ideal world, both your threads (assuming they don not share the connection) should be allowed to read and write at their convenience. Why bother with timing with Sleep if you can avoid it?



Next, you are correctly using transactions. Have you looked into the three different behaviours for transactions? https://sqlite.org/lang_transaction.html



That however does not solve the issue that thread 1 might try to get a lock while thread 2 is. For this, see PRAGMA busy_timeout. Simply put the pragma on each connection to, say, 1000 (ms). If thread 2 has locked the database and thread 1 tries to get a lock, it will simply wait for 1000 ms until failing with a timeout error. (https://sqlite.org/pragma.html#pragma_busy_timeout)






share|improve this answer
























  • I am aware of busy_timeout - This will cause exceptions for the thread waiting to obtain the lock for the database. I don't want that. I want to load balance the work between two threads

    – ekalchev
    Nov 28 '18 at 13:49











Your Answer






StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});














draft saved

draft discarded


















StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53520346%2fconcurrency-issue-with-two-sqlite-connections-in-two-different-threads%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown

























2 Answers
2






active

oldest

votes








2 Answers
2






active

oldest

votes









active

oldest

votes






active

oldest

votes









1














I don't think SQlite supports true concurrent write transactions... I've used "non-exclusive" transactions on Android and they're documented (I'm writing in my own words from memory) as allowing concurrent reads while there may be a write going on.



Now more to the point... Looking at SQLite transaction docs:



https://sqlite.org/lang_transaction.html




Thus with a deferred transaction, the BEGIN statement itself does
nothing to the filesystem. Locks are not acquired until the first read
or write operation. The first read operation against a database
creates a SHARED lock and the first write operation creates a RESERVED
lock. Because the acquisition of locks is deferred until they are
needed, it is possible that another thread or process could create a
separate transaction and write to the database after the BEGIN on the
current thread has executed. If the transaction is immediate, then
RESERVED locks are acquired on all databases as soon as the BEGIN
command is executed, without waiting for the database to be used.




( emphasis - mine )



OK, so now we've learned that writing to a database requires a RESERVED lock.



Let's see what that is here:



https://sqlite.org/lockingv3.html#reserved_lock




A RESERVED lock means that the process is planning on writing to the
database file at some point in the future but that it is currently
just reading from the file. Only a single RESERVED lock may be active
at one time, though multiple SHARED locks can coexist with a single
RESERVED lock. RESERVED differs from PENDING in that new SHARED locks
can be acquired while there is a RESERVED lock.




OK, so this confirms that SQLite requires a RESERVED lock for writing to the database, and also tells us that only a single RESERVED lock may exist at a time -> only one transaction is allowed to have write access, others will wait.



Now if you're trying to interleave writing transactions from your two threads, each thread executing multiple (granular) transactions - then here is an idea:




  • Replacing Thread.Sleep with Thread.Yield


https://docs.microsoft.com/en-us/dotnet/api/system.threading.thread.yield



This may help with the issue of "the current writing thread's Sleep didn't cause a context switch to the thread we want".



Even with Yield there are still no guarantees that the OS / runtime will switch to the thread you want, but... perhaps worth a try, and at least you won't be artificially making your code run slower.




  • Given what we know about SQLite's "only one transaction is allowed to write", I'd consider the following pattern:


1 - Make a new thread whose job is to process database writes



2 - Queue up write operations to this thread from your current two threads



3 - Have the "operations" be self-contained / sufficient objects, containingf all the data they intend to write



4 - Finally, use a callback with a latch (in C# it's CountDownEvent I believe) to know when an operation is done, so your current threads can await completion



Then you'd only have one writing thread (as far as SQlite is concerned) and still have concurrency between your two current threads.



Pseudo code:



// Write thread

while (item = blockingQueue.getNextItemToWrite()) {
item.executeWrite(database)
item.signalCompletion()
}

// Thread 1

item = new WriteItem(some data that needs to be written)
WriteThread.enqeue(item)
item.awaitCompletion()

// Thread 2 - same as Thread 1


Where WriteItem base class has a CountDownEvent which is 1) awaited on by awaitCompletion and 2) signaled by signalCompletion.



I'm sure there is a way to wrap this into more elegant helper classes and maybe use async / await.






share|improve this answer


























  • Thank you for your answer, I already tried Thread.Yield but it doesn't help. About having a producer-consumer queue for writing to database. One of the reasons I didn't go that way was RESERVED locks. You can't know for sure if a query is going to need to write to the database until it actually execute. This way you can end up having db operations that will never need exclusive lock but will wait for other write operations in the queue.

    – ekalchev
    Nov 30 '18 at 18:01













  • But you don't have to use the "separate write thread" pattern everywhere, only where you need it...

    – Kostya Vasilyev
    Dec 1 '18 at 16:39
















1














I don't think SQlite supports true concurrent write transactions... I've used "non-exclusive" transactions on Android and they're documented (I'm writing in my own words from memory) as allowing concurrent reads while there may be a write going on.



Now more to the point... Looking at SQLite transaction docs:



https://sqlite.org/lang_transaction.html




Thus with a deferred transaction, the BEGIN statement itself does
nothing to the filesystem. Locks are not acquired until the first read
or write operation. The first read operation against a database
creates a SHARED lock and the first write operation creates a RESERVED
lock. Because the acquisition of locks is deferred until they are
needed, it is possible that another thread or process could create a
separate transaction and write to the database after the BEGIN on the
current thread has executed. If the transaction is immediate, then
RESERVED locks are acquired on all databases as soon as the BEGIN
command is executed, without waiting for the database to be used.




( emphasis - mine )



OK, so now we've learned that writing to a database requires a RESERVED lock.



Let's see what that is here:



https://sqlite.org/lockingv3.html#reserved_lock




A RESERVED lock means that the process is planning on writing to the
database file at some point in the future but that it is currently
just reading from the file. Only a single RESERVED lock may be active
at one time, though multiple SHARED locks can coexist with a single
RESERVED lock. RESERVED differs from PENDING in that new SHARED locks
can be acquired while there is a RESERVED lock.




OK, so this confirms that SQLite requires a RESERVED lock for writing to the database, and also tells us that only a single RESERVED lock may exist at a time -> only one transaction is allowed to have write access, others will wait.



Now if you're trying to interleave writing transactions from your two threads, each thread executing multiple (granular) transactions - then here is an idea:




  • Replacing Thread.Sleep with Thread.Yield


https://docs.microsoft.com/en-us/dotnet/api/system.threading.thread.yield



This may help with the issue of "the current writing thread's Sleep didn't cause a context switch to the thread we want".



Even with Yield there are still no guarantees that the OS / runtime will switch to the thread you want, but... perhaps worth a try, and at least you won't be artificially making your code run slower.




  • Given what we know about SQLite's "only one transaction is allowed to write", I'd consider the following pattern:


1 - Make a new thread whose job is to process database writes



2 - Queue up write operations to this thread from your current two threads



3 - Have the "operations" be self-contained / sufficient objects, containingf all the data they intend to write



4 - Finally, use a callback with a latch (in C# it's CountDownEvent I believe) to know when an operation is done, so your current threads can await completion



Then you'd only have one writing thread (as far as SQlite is concerned) and still have concurrency between your two current threads.



Pseudo code:



// Write thread

while (item = blockingQueue.getNextItemToWrite()) {
item.executeWrite(database)
item.signalCompletion()
}

// Thread 1

item = new WriteItem(some data that needs to be written)
WriteThread.enqeue(item)
item.awaitCompletion()

// Thread 2 - same as Thread 1


Where WriteItem base class has a CountDownEvent which is 1) awaited on by awaitCompletion and 2) signaled by signalCompletion.



I'm sure there is a way to wrap this into more elegant helper classes and maybe use async / await.






share|improve this answer


























  • Thank you for your answer, I already tried Thread.Yield but it doesn't help. About having a producer-consumer queue for writing to database. One of the reasons I didn't go that way was RESERVED locks. You can't know for sure if a query is going to need to write to the database until it actually execute. This way you can end up having db operations that will never need exclusive lock but will wait for other write operations in the queue.

    – ekalchev
    Nov 30 '18 at 18:01













  • But you don't have to use the "separate write thread" pattern everywhere, only where you need it...

    – Kostya Vasilyev
    Dec 1 '18 at 16:39














1












1








1







I don't think SQlite supports true concurrent write transactions... I've used "non-exclusive" transactions on Android and they're documented (I'm writing in my own words from memory) as allowing concurrent reads while there may be a write going on.



Now more to the point... Looking at SQLite transaction docs:



https://sqlite.org/lang_transaction.html




Thus with a deferred transaction, the BEGIN statement itself does
nothing to the filesystem. Locks are not acquired until the first read
or write operation. The first read operation against a database
creates a SHARED lock and the first write operation creates a RESERVED
lock. Because the acquisition of locks is deferred until they are
needed, it is possible that another thread or process could create a
separate transaction and write to the database after the BEGIN on the
current thread has executed. If the transaction is immediate, then
RESERVED locks are acquired on all databases as soon as the BEGIN
command is executed, without waiting for the database to be used.




( emphasis - mine )



OK, so now we've learned that writing to a database requires a RESERVED lock.



Let's see what that is here:



https://sqlite.org/lockingv3.html#reserved_lock




A RESERVED lock means that the process is planning on writing to the
database file at some point in the future but that it is currently
just reading from the file. Only a single RESERVED lock may be active
at one time, though multiple SHARED locks can coexist with a single
RESERVED lock. RESERVED differs from PENDING in that new SHARED locks
can be acquired while there is a RESERVED lock.




OK, so this confirms that SQLite requires a RESERVED lock for writing to the database, and also tells us that only a single RESERVED lock may exist at a time -> only one transaction is allowed to have write access, others will wait.



Now if you're trying to interleave writing transactions from your two threads, each thread executing multiple (granular) transactions - then here is an idea:




  • Replacing Thread.Sleep with Thread.Yield


https://docs.microsoft.com/en-us/dotnet/api/system.threading.thread.yield



This may help with the issue of "the current writing thread's Sleep didn't cause a context switch to the thread we want".



Even with Yield there are still no guarantees that the OS / runtime will switch to the thread you want, but... perhaps worth a try, and at least you won't be artificially making your code run slower.




  • Given what we know about SQLite's "only one transaction is allowed to write", I'd consider the following pattern:


1 - Make a new thread whose job is to process database writes



2 - Queue up write operations to this thread from your current two threads



3 - Have the "operations" be self-contained / sufficient objects, containingf all the data they intend to write



4 - Finally, use a callback with a latch (in C# it's CountDownEvent I believe) to know when an operation is done, so your current threads can await completion



Then you'd only have one writing thread (as far as SQlite is concerned) and still have concurrency between your two current threads.



Pseudo code:



// Write thread

while (item = blockingQueue.getNextItemToWrite()) {
item.executeWrite(database)
item.signalCompletion()
}

// Thread 1

item = new WriteItem(some data that needs to be written)
WriteThread.enqeue(item)
item.awaitCompletion()

// Thread 2 - same as Thread 1


Where WriteItem base class has a CountDownEvent which is 1) awaited on by awaitCompletion and 2) signaled by signalCompletion.



I'm sure there is a way to wrap this into more elegant helper classes and maybe use async / await.






share|improve this answer















I don't think SQlite supports true concurrent write transactions... I've used "non-exclusive" transactions on Android and they're documented (I'm writing in my own words from memory) as allowing concurrent reads while there may be a write going on.



Now more to the point... Looking at SQLite transaction docs:



https://sqlite.org/lang_transaction.html




Thus with a deferred transaction, the BEGIN statement itself does
nothing to the filesystem. Locks are not acquired until the first read
or write operation. The first read operation against a database
creates a SHARED lock and the first write operation creates a RESERVED
lock. Because the acquisition of locks is deferred until they are
needed, it is possible that another thread or process could create a
separate transaction and write to the database after the BEGIN on the
current thread has executed. If the transaction is immediate, then
RESERVED locks are acquired on all databases as soon as the BEGIN
command is executed, without waiting for the database to be used.




( emphasis - mine )



OK, so now we've learned that writing to a database requires a RESERVED lock.



Let's see what that is here:



https://sqlite.org/lockingv3.html#reserved_lock




A RESERVED lock means that the process is planning on writing to the
database file at some point in the future but that it is currently
just reading from the file. Only a single RESERVED lock may be active
at one time, though multiple SHARED locks can coexist with a single
RESERVED lock. RESERVED differs from PENDING in that new SHARED locks
can be acquired while there is a RESERVED lock.




OK, so this confirms that SQLite requires a RESERVED lock for writing to the database, and also tells us that only a single RESERVED lock may exist at a time -> only one transaction is allowed to have write access, others will wait.



Now if you're trying to interleave writing transactions from your two threads, each thread executing multiple (granular) transactions - then here is an idea:




  • Replacing Thread.Sleep with Thread.Yield


https://docs.microsoft.com/en-us/dotnet/api/system.threading.thread.yield



This may help with the issue of "the current writing thread's Sleep didn't cause a context switch to the thread we want".



Even with Yield there are still no guarantees that the OS / runtime will switch to the thread you want, but... perhaps worth a try, and at least you won't be artificially making your code run slower.




  • Given what we know about SQLite's "only one transaction is allowed to write", I'd consider the following pattern:


1 - Make a new thread whose job is to process database writes



2 - Queue up write operations to this thread from your current two threads



3 - Have the "operations" be self-contained / sufficient objects, containingf all the data they intend to write



4 - Finally, use a callback with a latch (in C# it's CountDownEvent I believe) to know when an operation is done, so your current threads can await completion



Then you'd only have one writing thread (as far as SQlite is concerned) and still have concurrency between your two current threads.



Pseudo code:



// Write thread

while (item = blockingQueue.getNextItemToWrite()) {
item.executeWrite(database)
item.signalCompletion()
}

// Thread 1

item = new WriteItem(some data that needs to be written)
WriteThread.enqeue(item)
item.awaitCompletion()

// Thread 2 - same as Thread 1


Where WriteItem base class has a CountDownEvent which is 1) awaited on by awaitCompletion and 2) signaled by signalCompletion.



I'm sure there is a way to wrap this into more elegant helper classes and maybe use async / await.







share|improve this answer














share|improve this answer



share|improve this answer








edited Nov 29 '18 at 21:49

























answered Nov 29 '18 at 21:02









Kostya VasilyevKostya Vasilyev

367317




367317













  • Thank you for your answer, I already tried Thread.Yield but it doesn't help. About having a producer-consumer queue for writing to database. One of the reasons I didn't go that way was RESERVED locks. You can't know for sure if a query is going to need to write to the database until it actually execute. This way you can end up having db operations that will never need exclusive lock but will wait for other write operations in the queue.

    – ekalchev
    Nov 30 '18 at 18:01













  • But you don't have to use the "separate write thread" pattern everywhere, only where you need it...

    – Kostya Vasilyev
    Dec 1 '18 at 16:39



















  • Thank you for your answer, I already tried Thread.Yield but it doesn't help. About having a producer-consumer queue for writing to database. One of the reasons I didn't go that way was RESERVED locks. You can't know for sure if a query is going to need to write to the database until it actually execute. This way you can end up having db operations that will never need exclusive lock but will wait for other write operations in the queue.

    – ekalchev
    Nov 30 '18 at 18:01













  • But you don't have to use the "separate write thread" pattern everywhere, only where you need it...

    – Kostya Vasilyev
    Dec 1 '18 at 16:39

















Thank you for your answer, I already tried Thread.Yield but it doesn't help. About having a producer-consumer queue for writing to database. One of the reasons I didn't go that way was RESERVED locks. You can't know for sure if a query is going to need to write to the database until it actually execute. This way you can end up having db operations that will never need exclusive lock but will wait for other write operations in the queue.

– ekalchev
Nov 30 '18 at 18:01







Thank you for your answer, I already tried Thread.Yield but it doesn't help. About having a producer-consumer queue for writing to database. One of the reasons I didn't go that way was RESERVED locks. You can't know for sure if a query is going to need to write to the database until it actually execute. This way you can end up having db operations that will never need exclusive lock but will wait for other write operations in the queue.

– ekalchev
Nov 30 '18 at 18:01















But you don't have to use the "separate write thread" pattern everywhere, only where you need it...

– Kostya Vasilyev
Dec 1 '18 at 16:39





But you don't have to use the "separate write thread" pattern everywhere, only where you need it...

– Kostya Vasilyev
Dec 1 '18 at 16:39













0














Take a look at busy_timeout. In the ideal world, both your threads (assuming they don not share the connection) should be allowed to read and write at their convenience. Why bother with timing with Sleep if you can avoid it?



Next, you are correctly using transactions. Have you looked into the three different behaviours for transactions? https://sqlite.org/lang_transaction.html



That however does not solve the issue that thread 1 might try to get a lock while thread 2 is. For this, see PRAGMA busy_timeout. Simply put the pragma on each connection to, say, 1000 (ms). If thread 2 has locked the database and thread 1 tries to get a lock, it will simply wait for 1000 ms until failing with a timeout error. (https://sqlite.org/pragma.html#pragma_busy_timeout)






share|improve this answer
























  • I am aware of busy_timeout - This will cause exceptions for the thread waiting to obtain the lock for the database. I don't want that. I want to load balance the work between two threads

    – ekalchev
    Nov 28 '18 at 13:49
















0














Take a look at busy_timeout. In the ideal world, both your threads (assuming they don not share the connection) should be allowed to read and write at their convenience. Why bother with timing with Sleep if you can avoid it?



Next, you are correctly using transactions. Have you looked into the three different behaviours for transactions? https://sqlite.org/lang_transaction.html



That however does not solve the issue that thread 1 might try to get a lock while thread 2 is. For this, see PRAGMA busy_timeout. Simply put the pragma on each connection to, say, 1000 (ms). If thread 2 has locked the database and thread 1 tries to get a lock, it will simply wait for 1000 ms until failing with a timeout error. (https://sqlite.org/pragma.html#pragma_busy_timeout)






share|improve this answer
























  • I am aware of busy_timeout - This will cause exceptions for the thread waiting to obtain the lock for the database. I don't want that. I want to load balance the work between two threads

    – ekalchev
    Nov 28 '18 at 13:49














0












0








0







Take a look at busy_timeout. In the ideal world, both your threads (assuming they don not share the connection) should be allowed to read and write at their convenience. Why bother with timing with Sleep if you can avoid it?



Next, you are correctly using transactions. Have you looked into the three different behaviours for transactions? https://sqlite.org/lang_transaction.html



That however does not solve the issue that thread 1 might try to get a lock while thread 2 is. For this, see PRAGMA busy_timeout. Simply put the pragma on each connection to, say, 1000 (ms). If thread 2 has locked the database and thread 1 tries to get a lock, it will simply wait for 1000 ms until failing with a timeout error. (https://sqlite.org/pragma.html#pragma_busy_timeout)






share|improve this answer













Take a look at busy_timeout. In the ideal world, both your threads (assuming they don not share the connection) should be allowed to read and write at their convenience. Why bother with timing with Sleep if you can avoid it?



Next, you are correctly using transactions. Have you looked into the three different behaviours for transactions? https://sqlite.org/lang_transaction.html



That however does not solve the issue that thread 1 might try to get a lock while thread 2 is. For this, see PRAGMA busy_timeout. Simply put the pragma on each connection to, say, 1000 (ms). If thread 2 has locked the database and thread 1 tries to get a lock, it will simply wait for 1000 ms until failing with a timeout error. (https://sqlite.org/pragma.html#pragma_busy_timeout)







share|improve this answer












share|improve this answer



share|improve this answer










answered Nov 28 '18 at 13:34









MrGumbleMrGumble

2,14811123




2,14811123













  • I am aware of busy_timeout - This will cause exceptions for the thread waiting to obtain the lock for the database. I don't want that. I want to load balance the work between two threads

    – ekalchev
    Nov 28 '18 at 13:49



















  • I am aware of busy_timeout - This will cause exceptions for the thread waiting to obtain the lock for the database. I don't want that. I want to load balance the work between two threads

    – ekalchev
    Nov 28 '18 at 13:49

















I am aware of busy_timeout - This will cause exceptions for the thread waiting to obtain the lock for the database. I don't want that. I want to load balance the work between two threads

– ekalchev
Nov 28 '18 at 13:49





I am aware of busy_timeout - This will cause exceptions for the thread waiting to obtain the lock for the database. I don't want that. I want to load balance the work between two threads

– ekalchev
Nov 28 '18 at 13:49


















draft saved

draft discarded




















































Thanks for contributing an answer to Stack Overflow!


  • Please be sure to answer the question. Provide details and share your research!

But avoid



  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.


To learn more, see our tips on writing great answers.




draft saved


draft discarded














StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53520346%2fconcurrency-issue-with-two-sqlite-connections-in-two-different-threads%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown







Popular posts from this blog

A CLEAN and SIMPLE way to add appendices to Table of Contents and bookmarks

Calculate evaluation metrics using cross_val_predict sklearn

Insert data from modal to MySQL (multiple modal on website)