Re: [sqlite] Getting a notification when a write lock is released.
test user, on Friday, August 16, 2019 02:29 PM, wrote... > > Thanks for the example José. You're welcome. Just thought I would provide some idea... :-) josé ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting a notification when a write lock is released.
Thanks for the example José.
I was thinking of a more general method that would work when I do not
control all of the clients.
Your example would only work when all clients are aware of and use the
locking logic.
On Fri, Aug 16, 2019 at 3:39 PM Jose Isaias Cabrera
wrote:
>
> test user, on Thursday, August 15, 2019 07:35 PM, wrote...
>
> > The reason for the notification is to minimize time spent waiting.
>
> I will tell you what I did with 10 PMs working with a shared windows drive
> with an SQLite DB. But, take it with a grain of salt, unless you have
> high-blood pressure, which if it is so, may suggest a salt substitute? :-)
>
> Before every write to the DB, I would call a function that would check if
> someone was writing to the DB:
>
> while (SharedDBBlocked(false))
> std.c.time.msleep(500); // waits 1/2 second
>
> This is what SharedDBBlocked did...
>
> char[] SharedDBBlocked(bool ShowMsgBox)
> {
> char[] ttdir = std.path.getDirName(sqldb); // =
> r"L:\Data\OpenJobsTool\AllOpenProjs.db";
> ttdir = ttdir ~ "\\" ~ "dbLockedBy.txt";// checks for a file in the
> same spot where the sharedDB is
> char[] who = null;
> if (std.file.exists(ttdir)) // someone is writing to the DB
> {
> try
> {
> who = cast(char[]) ttdir.read();
> }
> catch (FileException e)
> {
> return who;
> }
> if (who == pm["FirstName"]) // The PMs lack of patience (3-10
> seconds). Long story.
> {
> if (DeleteFile(ttdir, eStr))
> info.text = ttdir[std.string.rfind(ttdir,r"\") .. $] ~ " file
> deleted.";
> return null;
> }
> else
> {
> char[] t = who ~ " is writing to the SharedDB. Waiting for
> release...";
> error.text = t;
> if (ShowMsgBox)
> msgBox(t);
> return who;
> }
> }
> return who;
> }
>
> Once this was DB was release, you would call for,
>
> LockDBForDataWriting();
>
> to take control of the DB. This is what it contains...
>
> char[] LockDBForDataWriting()
> {
> while (SharedDBBlocked(false)) //check again to make sure...
> std.c.time.msleep(1500);
> error.text = "";
> char[] ttdir = std.path.getDirName(sqldba); // =
> r"L:\Data\OpenJobsTool\AllOpenProjs.db";
> ttdir = ttdir ~ "\\dbLockedBy.txt";
> char[] t = "";;
> if (!std.file.exists(ttdir))
> {
> try
> {
> ttdir.write(pm["FirstName"]);
> t = pm["FirstName"];
> }
> catch (FileException e)
> {
> ttdir.write(GetUserName());
> t = GetUserName();
> }
> }
> return t;
> }
>
> Once you have control, do some work on the DB...
>
> string q = "INSERT ...; ";
> try
> {
> wdb.execute(q);
> }
> catch (DBIException dbe)
> {
> UnLockDBForDataWriting();
> msgBox("ERR605: Could not ...:" ~ dbe.toString());
> return 1;
> }
>
> UnLockDBForDataWriting();
> return 0;
>
> And that is it. Of course, this is a primitive and slow way of doing it,
> but these were inpatient PMs, and it worked for a long time. If your
> writters are not human, the wait times could be lowered and it would be
> made faster. Just FYI. Thanks.
>
> josé
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting a notification when a write lock is released.
test user, on Thursday, August 15, 2019 07:35 PM, wrote...
> The reason for the notification is to minimize time spent waiting.
I will tell you what I did with 10 PMs working with a shared windows drive with
an SQLite DB. But, take it with a grain of salt, unless you have high-blood
pressure, which if it is so, may suggest a salt substitute? :-)
Before every write to the DB, I would call a function that would check if
someone was writing to the DB:
while (SharedDBBlocked(false))
std.c.time.msleep(500); // waits 1/2 second
This is what SharedDBBlocked did...
char[] SharedDBBlocked(bool ShowMsgBox)
{
char[] ttdir = std.path.getDirName(sqldb); // =
r"L:\Data\OpenJobsTool\AllOpenProjs.db";
ttdir = ttdir ~ "\\" ~ "dbLockedBy.txt";// checks for a file in the same
spot where the sharedDB is
char[] who = null;
if (std.file.exists(ttdir)) // someone is writing to the DB
{
try
{
who = cast(char[]) ttdir.read();
}
catch (FileException e)
{
return who;
}
if (who == pm["FirstName"]) // The PMs lack of patience (3-10 seconds).
Long story.
{
if (DeleteFile(ttdir, eStr))
info.text = ttdir[std.string.rfind(ttdir,r"\") .. $] ~ " file deleted.";
return null;
}
else
{
char[] t = who ~ " is writing to the SharedDB. Waiting for release...";
error.text = t;
if (ShowMsgBox)
msgBox(t);
return who;
}
}
return who;
}
Once this was DB was release, you would call for,
LockDBForDataWriting();
to take control of the DB. This is what it contains...
char[] LockDBForDataWriting()
{
while (SharedDBBlocked(false)) //check again to make sure...
std.c.time.msleep(1500);
error.text = "";
char[] ttdir = std.path.getDirName(sqldba); // =
r"L:\Data\OpenJobsTool\AllOpenProjs.db";
ttdir = ttdir ~ "\\dbLockedBy.txt";
char[] t = "";;
if (!std.file.exists(ttdir))
{
try
{
ttdir.write(pm["FirstName"]);
t = pm["FirstName"];
}
catch (FileException e)
{
ttdir.write(GetUserName());
t = GetUserName();
}
}
return t;
}
Once you have control, do some work on the DB...
string q = "INSERT ...; ";
try
{
wdb.execute(q);
}
catch (DBIException dbe)
{
UnLockDBForDataWriting();
msgBox("ERR605: Could not ...:" ~ dbe.toString());
return 1;
}
UnLockDBForDataWriting();
return 0;
And that is it. Of course, this is a primitive and slow way of doing it, but
these were inpatient PMs, and it worked for a long time. If your writters are
not human, the wait times could be lowered and it would be made faster. Just
FYI. Thanks.
josé
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting a notification when a write lock is released.
Thanks Simon, > You can use any other combination that suits you. Perhaps set a short > timeout, after which SQLite calls your busy handler, which can do whatever > it wants then return SQLITE_BUSY to your program. When the short timeout > gets exhausted, SQLite calls your own busy handler, and /that's/ your > notification. > > None of these would use the hint system or try to monitor files directly. > As you see, there's an existing way to monitor locks. You don't need to > abuse a different notification which isn't really suited to this purpose. The reason for the notification is to minimize time spent waiting. Lets say you can categorize the time spent waiting for a lock into these sets: - A. Time waiting whilst lock is locked. - B. Time waiting whilst lock is unlocked. With the current locking system, if you have many processes contending for write locks, time spent in set B will be > 0. With a notification when the lock is released, B can equal 0 for the duration of the system uptime. I understand the current lock system works well, and that this is a more complex system. Regarding your short timeout suggestion: Is polling the lock frequently in very short intervals resource intensive? Is SQLITE_ENABLE_SETLK_TIMEOUT the best way to try and implement this in my own library? On Thu, Aug 15, 2019 at 11:08 PM Simon Slavin wrote: > On 15 Aug 2019, at 10:43pm, test user > wrote: > > > Currently the API lets you set a timeout. Does this just retry again > after a set amount of time? > > SQLite's built-in busy handler (which it uses unless you tell it to use > yours instead) repeatedly backs off and retries until the timeout you set > is complete. The amount of time it backs off for is decided internally and > you cannot depend on it being the same amount of time every time. The > entire time the busy handler takes should never much exceed the amount of > time you set as your timeout. > > > But I was thinking more along the lines of keeping BUSY and the current > locking system as is, but using the notification as a hint, that is > possibly unreliable. > > > > E.g. if BUSY (try again (after x seconds OR when hint arrives)) > > The assumption behind SQLite is that you will do one of three things. The > first of them is what most users do: > > 1) Set a long timeout, but not set your own busy handler. Then let SQLite > handle the wait-and-retry loop until it finally gives up and returns > SQLITE_BUSY. Then your program announces that the database is inaccessible > and quits (or gives up that function). > > 2) Set no timeout and no busy handler. Receive and handle SQLITE_BUSY by > doing whatever the program finds suitable. Perhaps implement your own > wait-and-retry loop, perhaps do something a lot more sophisticated, > appropriate to whatever the user expects. > > 3) Set no timeout and implement your own busy handler. > > You can use any other combination that suits you. Perhaps set a short > timeout, after which SQLite calls your busy handler, which can do whatever > it wants then return SQLITE_BUSY to your program. When the short timeout > gets exhausted, SQLite calls your own busy handler, and /that's/ your > notification. > > None of these would use the hint system or try to monitor files directly. > As you see, there's an existing way to monitor locks. You don't need to > abuse a different notification which isn't really suited to this purpose. > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting a notification when a write lock is released.
On 15 Aug 2019, at 10:43pm, test user wrote: > Currently the API lets you set a timeout. Does this just retry again after a > set amount of time? SQLite's built-in busy handler (which it uses unless you tell it to use yours instead) repeatedly backs off and retries until the timeout you set is complete. The amount of time it backs off for is decided internally and you cannot depend on it being the same amount of time every time. The entire time the busy handler takes should never much exceed the amount of time you set as your timeout. > But I was thinking more along the lines of keeping BUSY and the current > locking system as is, but using the notification as a hint, that is possibly > unreliable. > > E.g. if BUSY (try again (after x seconds OR when hint arrives)) The assumption behind SQLite is that you will do one of three things. The first of them is what most users do: 1) Set a long timeout, but not set your own busy handler. Then let SQLite handle the wait-and-retry loop until it finally gives up and returns SQLITE_BUSY. Then your program announces that the database is inaccessible and quits (or gives up that function). 2) Set no timeout and no busy handler. Receive and handle SQLITE_BUSY by doing whatever the program finds suitable. Perhaps implement your own wait-and-retry loop, perhaps do something a lot more sophisticated, appropriate to whatever the user expects. 3) Set no timeout and implement your own busy handler. You can use any other combination that suits you. Perhaps set a short timeout, after which SQLite calls your busy handler, which can do whatever it wants then return SQLITE_BUSY to your program. When the short timeout gets exhausted, SQLite calls your own busy handler, and /that's/ your notification. None of these would use the hint system or try to monitor files directly. As you see, there's an existing way to monitor locks. You don't need to abuse a different notification which isn't really suited to this purpose. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting a notification when a write lock is released.
> > SQLite could support this in theory. But if the process holding the > lock is hung, that would hang the process waiting on the look too. > > Getting SQLITE_BUSY is annoying, but it is not nearly as annoying as > getting a > hung process. > > I am not aware of a way to do a blocking file lock with a timeout that > is portable across operating systems. Ok thanks, this makes sense for reliability/portability. But I was thinking more along the lines of keeping BUSY and the current locking system as is, but using the notification as a hint, that is possibly unreliable. E.g. if BUSY (try again (after x seconds OR when hint arrives)) Is SQLITE_ENABLE_SETLK_TIMEOUT OK to use in production? So this will allow the busy handler to try again as soon as the lock is released? Are you sure that this is not the default case? Is the maximum retry > interval of about 250 milliseconds too long for your taste? I think it is the default case (if the lock is released, your busy handler will still wait until its current timeout to try again). Its not too long - everything works fine. This is probably premature optimisation. I'm just trying to understand the optimal way to implement writes. I have a program that may have two or more instances accessing the same file. Lets say I have these processes, each with their own write queue: p1: [a,b,c] p2: [d,e,f] p3: [g,h,i] The write queue works fine when I have one process: Each write completes, releases the lock, and the next write starts. There is 0 time spent waiting (for a lock to be released that is already released), and no BUSY is encountered. But if there are two or more processes each with their own write queue, each queue will contend with each other for the write lock, and maybe spend time waiting when the lock has already released. I want to be able to maintain the "0 time spent waiting" of the single process for multiple processes. I want to move writes though the queue as quickly as possible so they are persisted as soon as possible. There is a "write queue" because my program uses an event loop, and each queued item is a request from some point in the program to acquire a write lock with "BEGIN IMMEDIATE". On Thu, Aug 15, 2019 at 8:48 PM Keith Medcalf wrote: > > On Thursday, 15 August, 2019 13:11, test user < > example.com.use...@gmail.com> wrote: > > >If two processes are writing to the same db file, one will get a BUSY > >response if the other has locked it. > > >Currently the API lets you set a timeout. Does this just retry again > >after a set amount of time? > > timeout specifies the time after which to give up waiting for the lock. > There are multiple attempts to check the lock which is based on a > more-or-less exponential back-off algorithm. > The precision of the timeout and the frequency of the attempts depend on > whether the underlying OS supports fractional second waits or not and > whether of not the library knows that this is supported (ie, usleep rather > than just sleep). > > see src/main.c for the code for the sqliteDefaultBusyHandler. > > You can provide your own per-process global busy_handler if you do not > like the default handler. > > https://sqlite.org/c3ref/busy_handler.html > > >Or is it possible to get notified immediately when the lock has been > >released? Can I determine this by watching the file via OS system > >calls? > > If the underlying OS supports the lock-wait file control and if you > compile the code with SQLITE_ENABLE_SETLK_TIMEOUT defined, then the busy > handler and the locking mechanisms will utilize that OS capability to > manage the acquisition of the lock. > > >Basically Id like the second process to wait the smallest amount of > >time possible. With a timeout, it seems time is wasted waiting (if the > >lock has already been released). > > Are you sure that this is not the default case? Is the maximum retry > interval of about 250 milliseconds too long for your taste? > > -- > The fact that there's a Highway to Hell but only a Stairway to Heaven says > a lot about anticipated traffic volume. > > > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting a notification when a write lock is released.
On Thursday, 15 August, 2019 13:11, test user wrote: >If two processes are writing to the same db file, one will get a BUSY >response if the other has locked it. >Currently the API lets you set a timeout. Does this just retry again >after a set amount of time? timeout specifies the time after which to give up waiting for the lock. There are multiple attempts to check the lock which is based on a more-or-less exponential back-off algorithm. The precision of the timeout and the frequency of the attempts depend on whether the underlying OS supports fractional second waits or not and whether of not the library knows that this is supported (ie, usleep rather than just sleep). see src/main.c for the code for the sqliteDefaultBusyHandler. You can provide your own per-process global busy_handler if you do not like the default handler. https://sqlite.org/c3ref/busy_handler.html >Or is it possible to get notified immediately when the lock has been >released? Can I determine this by watching the file via OS system >calls? If the underlying OS supports the lock-wait file control and if you compile the code with SQLITE_ENABLE_SETLK_TIMEOUT defined, then the busy handler and the locking mechanisms will utilize that OS capability to manage the acquisition of the lock. >Basically Id like the second process to wait the smallest amount of >time possible. With a timeout, it seems time is wasted waiting (if the >lock has already been released). Are you sure that this is not the default case? Is the maximum retry interval of about 250 milliseconds too long for your taste? -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting a notification when a write lock is released.
On 8/15/19, test user wrote: > Hello, > > If two processes are writing to the same db file, one will get a BUSY > response if the other has locked it. > > Currently the API lets you set a timeout. Does this just retry again after > a set amount of time? Yes. It retries multiple times, increasing the wait time in between each attempt. > > Or is it possible to get notified immediately when the lock has been > released? Can I determine this by watching the file via OS system calls? > > Basically Id like the second process to wait the smallest amount of time > possible. With a timeout, it seems time is wasted waiting (if the lock has > already been released). SQLite could support this in theory. But if the process holding the lock is hung, that would hang the process waiting on the look too. And then we would get lots of complaints about how "SQLite hangs!" when in fact it was some other application that starts a transaction, then hangs doing unrelated processing, and causes one or more SQLite processes trying to access the same file to hang as well. Getting SQLITE_BUSY is annoying, but it is not nearly as annoying as getting a hung process. I am not aware of a way to do a blocking file lock with a timeout that is portable across operating systems. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Getting a notification when a write lock is released.
Hello, If two processes are writing to the same db file, one will get a BUSY response if the other has locked it. Currently the API lets you set a timeout. Does this just retry again after a set amount of time? Or is it possible to get notified immediately when the lock has been released? Can I determine this by watching the file via OS system calls? Basically Id like the second process to wait the smallest amount of time possible. With a timeout, it seems time is wasted waiting (if the lock has already been released). Thanks. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
