while(sqlite database is locked) loop >> help

Question

0.00/5 (No votes)

See more:

Hi.

I have a sqlite data base which has a table named Customers. And I implemented few functions to change the data about a customer.

They are like

C#

changeName(int id, string newName);
chgangeTP(int id, string newTP);
changeAddress(int id, string newAddress);
changeEmail(int id, string newEmail);
changeNotes(int id, string newNotes);

and my problem is sqlite locks the database for editing purposes, as they say.

So if user wants to change multiple columns at once, it would cause an error.

for example >>

if user tried to edit both name and address, the program first calls changeName() function which locks the database to edit the name and then unlocks, and then it calls changeAddress() function which locks the database againg to edit the address and then unlocks again.

if program would be a bit faster than the database locking and unlocking task, the program will be trying to edit the address before it unlocks after editing the name.

So I want to know if there's a way to check if sqlite database or data table is locked.
Just like >>

C#

while(myDatabase.Locked)
 thread.Sleep(300);

//unlocked
changeAddress(500, "hell");

thanks in advance !

Posted 20-Sep-14 21:28pm

Mark

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2014-09-20T21:45:00

Solution 1

If your code is locking the DB, that's because your code is not releasing it: you need to either use a common connection which you never release, or release the connection properly after each access. Adding waiting on your thread until it's released will not help that - becuas ethe thread that is holding them is the one that is waiting... :laugh:

At the moment, I'd guess that each of your "changeXXX" methods tries to connect to the DB, make the change, and exit - which doesn't release the DB file until the garbage collector gets round to it.
So change your code to use using blocks round the Connection object (and the Command objects, and so forth) to ensure that the file is released properly when you are "finished" with it.

Posted 20-Sep-14 21:45pm

OriginalGriff

Comments

Mark 21-Sep-14 3:51am

u say, if I use a single connection for all of my commands, it's okay to execute multiple commands at once and change the Address while another command is changing the name ?

OriginalGriff 21-Sep-14 4:08am

Well - unless you are multithreading (and for a single user app you probably aren't) you can't do that anyway, as the single thread will execute the instructions serially.
So yes, you can open a connection when you start the app, and hold it open until you exit. That would block any other apps from accessing the file, which is generally ok for SQLite databases - except that backup systems can't copy the file either. You will probably have to make sure you close and dispose DataReaders (as you can't attach commands to a connection that has an open reader).

I'd probably open-process-close manually, and open-process-process-process-close for multiple operations myself rather than hold the DB open (I like backups, lots of backups! :laugh:)

Mark 21-Sep-14 5:33am

Sorry. But I still dont get that completely.. Just answer me to this...

if I change my code to this >>

using(SQLiteConnection con = new SQLiteConnection(conString))
{
con.Open();
using(SQLiteCommand commandToChangeName = new SQLiteCommand(sql1, con))
{
commandToChangeName.ExecuteNonQuery();
}
using (SQLiteCommand commandToChangeAddress = new SQLiteCommand(sql2, con))
{
commandToChangeAddress.ExecuteNonQuery();
}
}

1. Would it make sure that the second sqlite command (which changes the address) executes after the first one executed and it's editings has completed ?

OriginalGriff 21-Sep-14 6:01am

Yes.
The only change I'd make is to add a Transaction round both INSERT / UPDATE Commands, so that if one fails the other can be "undone" - if your DB needs both changes correct, then it could be left in an "odd" state otherwise.