Re: [sqlite] New word to replace "serverless"
Someone should put all the proposals into a vote. The voting system could be driven by a serverless database I presume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] New word to replace "serverless"
stack-capturing It captures the stack of the host. stack-dependent pointer-based ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sanitising user input for FTS5 MATCH parameter
Thanks for the details Dan On Sat, 21 Dec 2019 at 18:40, Dan Kennedy wrote: > > On 20/12/62 22:03, test user wrote: > > Hello, > > > > I have a search box on a website that uses FTS5/MATCH. > > > > MATCH seems to take its own custom language for matching. > > > > 1. Is it safe to just pass the users query to MATCH ? via the SQLite bind > > FFI? > > Users could specify a query that uses excessive resources. In > particular, prefix searches for very common prefixes on large databases > can use a lot of memory. I think it's otherwise safe though. > > > - This would give them full access to the FTS5 matching language. > > > > 2. If not, how should I be sanitising user input? > > > > - E.g. How can I transform a string of words and text into a query? What > > characters should I be removing or escaping? How can I prevent them using > > the FTS5 keywords "AND" "OR" etc? > It really depends on what you want to allow. And how you want the query > interpreted. If you want all input to be treated as a single phrase, > enclose it in double-quotes, doubling any embedded " characters SQL > style. Or, if you wanted the input treated as a list of terms separated > by implicit AND, split the input on whitespace and then enclose each > term in double-quotes. Details here: > >https://www.sqlite.org/fts5.html#full_text_query_syntax > > Dan. > > > > > > > > Thanks > > ___ > > 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 > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sanitising user input for FTS5 MATCH parameter
Yeh but it doesn’t explicitly say anything about how secure it is to pass a string directly to “match”. Other scalar values that are passed to a query via binding are safe as they are just data saved to the DB file. But as the MATCH x string contains a language it is going to be interpreted or compiled which is why I was asking how safe that would be. On Fri, 20 Dec 2019 at 16:05, Jose Isaias Cabrera wrote: > > test user, on Friday, December 20, 2019 10:03 AM, wrote... > > > > Hello, > > > > I have a search box on a website that uses FTS5/MATCH. > > > > MATCH seems to take its own custom language for matching. > > > > 1. Is it safe to just pass the users query to MATCH ? via the SQLite bind > > FFI? > > > > - This would give them full access to the FTS5 matching language. > > > > 2. If not, how should I be sanitising user input? > > > > - E.g. How can I transform a string of words and text into a query? What > > characters should I be removing or escaping? How can I prevent them using > > the FTS5 keywords "AND" "OR" etc? > > Have you taken a look at the FTS5 site[1]? It has lots of information > there that may be helpful. > > josé > > [1] https://www.sqlite.org/fts5.html > > ___ > 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
[sqlite] Sanitising user input for FTS5 MATCH parameter
Hello, I have a search box on a website that uses FTS5/MATCH. MATCH seems to take its own custom language for matching. 1. Is it safe to just pass the users query to MATCH ? via the SQLite bind FFI? - This would give them full access to the FTS5 matching language. 2. If not, how should I be sanitising user input? - E.g. How can I transform a string of words and text into a query? What characters should I be removing or escaping? How can I prevent them using the FTS5 keywords "AND" "OR" etc? Thanks ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Securing user supplied SQL statements in a single process
Hello, How can I secure user supplied SQL statements in a single process? For example, if I had a public web service that allows users to create their own SQL strings that I then run in a single server process, what are the chances that they would be able to obtain general remote code execution? I saw a security flaw a while back, and most people mentioned that “you should not run user supplied SQL statements”. Is there a way to do this using only SQLite (without spawning many processes or using OS level isolation)? Thanks ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [draft patch] interface for retrieving values of bound parameters
Wouldn’t your program already know what the values are as it passed them over the FFI initially? Why not hold onto that state? On Mon, 11 Nov 2019 at 17:57, x wrote: > Is http://www.sqlite.org/c3ref/expanded_sql.html no use to you? > > > > > From: sqlite-users on > behalf of tab > Sent: Monday, November 11, 2019 5:26:42 PM > To: sqlite-users@mailinglists.sqlite.org < > sqlite-users@mailinglists.sqlite.org> > Subject: [sqlite] [draft patch] interface for retrieving values of bound > parameters > > Hi all, > > It'd be handy to be able to retrieve params previously bound to a > statement in the C API. Per the advice on the SQLite copyright info page, > this is much more of a suggestion than a full patch, though it is > functional for binding and retrieving an sqlite_value* (but, for example, > there might be further implications not considered here in allowing the > contents of aVar to be used directly.) There wouldn't be much value in > maintaining a fork for something like this, so I wanted to put that out > here on the mailing list to see if it's something that might be considered > for mainline. > > ___ > 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 > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Opposite of SQLite
CAHeavy Chaotic Answer Heavy On Thu, 10 Oct 2019 at 20:42, Simon Slavin wrote: > On 10 Oct 2019, at 7:55pm, Ned Fleming wrote: > > > SQLessLite > > SQDietStartsMonday > ___ > 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] Copy-on-write VFS
Hello Fredrik, Why does it need to be part of a VFS instead of using a file system with COW like ZFS? On Fri, 4 Oct 2019 at 12:18, Fredrik Larsen wrote: > Hi > > A copy-on-write IO-path where data is split into static and dynamic parts > (think snapshots for storage) would be very helpful for our project, . This > would simplify backups, testing, moving data around in a multinode > environment, etc. > > Does something like this exist for sqlite? In my head this sounds like an > relative easy feature to add as IO-stuff is already centralized in the VFS > layer. Maybe a new COW-VFS? > > Fredrik > ___ > 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] SQLITE_BUSY, database is locked in "PRAGMA journal_mode"
Thanks Rowan, this is useful.
Is it normal to get a `SQLITE_BUSY_RECOVERY` response from an API when:
- No processes have crashed.
- All API uses close/finalize their db/stmt objects.
I am testing some code I wrote to make sure it retires on BUSY by creating
many processes that acquire locks with `BEGIN IMMEDIATE`.
`SQLITE_BUSY_RECOVERY` occurs at around 7 processes requesting a write
lock, but not at 5 processes or below.
Does this indicate corrupted data?
On Mon, Sep 2, 2019 at 2:45 AM Rowan Worth wrote:
> On Fri, 30 Aug 2019 at 04:18, test user
> wrote:
>
> > B. Is there any method for determining lock transitions for connections?
> > - Is there an API?
> > - Would it be possible to use dtrace to instrument SQLite to detect
> > lock transitions?
> > - Where should I be looking?
> >
>
> On unix sqlite uses fcntl() with cmd=F_SETLK on specific byte locations to
> acquire locks -- I'm not familiar with dtrace, but I've used strace + sed
> to watch sqlite lock activity before. eg:
>
> #!/bin/sh
>
> PID=$1
>
> replace() {
> echo "s#F_SETLK, {type=F_$1, whence=SEEK_SET, start=$2, len=$3}#$4#"
> }
>
> strace -Ttt -ff -e trace=fcntl -p $PID 2>&1 |
> sed \
> -e "$(replace RDLCK 1073741824 1 acquireR{PENDING})" \
> -e "$(replace RDLCK 1073741825 1 acquireR{RESERVED})" \
> -e "$(replace RDLCK 1073741826 510 acquire{SHARED})" \
> -e "$(replace WRLCK 1073741824 1 acquireW{PENDING})" \
> -e "$(replace WRLCK 1073741825 1 acquireW{RESERVED})" \
> -e "$(replace WRLCK 1073741826 510 acquire{EXCLUSIVE})" \
> -e "$(replace UNLCK 1073741824 2 release{PENDING+RESERVED})" \
> -e "$(replace UNLCK 1073741824 1 release{PENDING})" \
> -e "$(replace UNLCK 1073741825 1 release{RESERVED})" \
> -e "$(replace UNLCK 1073741826 510 release{SHARED/EXCLUSIVE})" \
> -e "$(replace UNLCK 0 0 release{ALL})"
>
> -Rowan
> ___
> 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] SQLITE_BUSY, database is locked in "PRAGMA journal_mode"
Just some more details to clarify the issue: If I do a `BEGIN IMMEDIATE` on one connection, and then a `PRGAMA journal_mode` on another, BUSY is not returned (as expected). But if I have around 7 connections contending for a write lock via `BEGIN IMMEDIATE`, and a different connection runs `PRGAMA journal_mode` (with no `BEGIN`), it returns BUSY (not expected). I also occasionally get a "SQLITE_BUSY_RECOVERY", but all API usages are using finalize/close to give back any sqlite API resources. *Questions:* A. In which cases will BUSY be returned for read only queries when in WAL mode? - I assumed this was never, as WAL mode allows many concurrent readers. - How can I find out exceptions to this rule? B. Is there any method for determining lock transitions for connections? - Is there an API? - Would it be possible to use dtrace to instrument SQLite to detect lock transitions? - Where should I be looking? Id really appreciate any pointers, Thanks. On Wed, Aug 28, 2019 at 9:46 PM test user wrote: > Hello, > > Im getting this message in the log: > > `SQLITE_BUSY, database is locked in "PRAGMA journal_mode"` > > I get this response when running the query `PRAGMA journal_mode`. > > The file is in journal_mode=WAL. > > Another connection holds a write transaction. > > Seeing as `PRAGMA journal_mode` is just a read, why would this return BUSY? > > I assumed that in WAL mode you can have many reads and a single writer at > the same time? > > Thanks > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] dqlite 1.0.0 - SQLite replication and failover library
Hey Free, Looks like an interesting project. Is there a blog or docs about the reasons for the move from Go to C? Also what types of systems would utilise dqlite? Are there current users? Thanks On Thu, 29 Aug 2019 at 11:41, Free Ekanayaka wrote: > Hi, > > following up from my previous post back in 2017 [0], I'd like to > announce version 1.0.0 of dqlite, a C library that brings data > replication and high-availability to SQLite, using the Raft consensus > algorithm. > > The biggest change is that Go is not used anymore, the engine itself is > all pure C now. > > It still requires to apply a patch to SQLite, but it's a rather tiny one > that just adds a few hooks when writing to the Write-Ahead log. > > See https://dqlite.io for more details. > > Thanks again to the SQLite authors for their excellent work. > > Cheers, > > Free > > [0] > http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/2017-August/074384.html > ___ > 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
[sqlite] SQLITE_BUSY, database is locked in "PRAGMA journal_mode"
Hello, Im getting this message in the log: `SQLITE_BUSY, database is locked in "PRAGMA journal_mode"` I get this response when running the query `PRAGMA journal_mode`. The file is in journal_mode=WAL. Another connection holds a write transaction. Seeing as `PRAGMA journal_mode` is just a read, why would this return BUSY? I assumed that in WAL mode you can have many reads and a single writer at the same time? Thanks ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Attached databases and union view.
What language/binding library are you using? On Thu, 22 Aug 2019 at 16:45, Peter da Silva wrote: > Database is on tmpfs and periodically snapshotted to SSD. There are > bottlenecks upstream of sqlite that we can see in traces. > > On Thu, Aug 22, 2019 at 10:36 AM Warren Young wrote: > > > On Aug 22, 2019, at 9:27 AM, Peter da Silva wrote: > > > > > > Have an existing application that's pushing the limit > > > > If the limit is in hardware, shards won’t help. > > > > For example, a SQLite DB on a 7200 RPM spinning disk is limited to about > > 60 transactions per second under the stock SQLite fsync logic, since each > > takes 2 revolutions to commit. (One to write to the journal, and one to > > commit the journal entry.) Writes to multiple shards only get to share a > > platter rotation if there is no seek between writes. > > > > The limits are higher for SSDs, but there’s still a low limit on the > > number of parallel writes. > > ___ > > 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 > ___ 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.
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.
> > 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
[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
[sqlite] Why does WAL prevent the need for async IO?
Hello, On this page: https://www.sqlite.org/asyncvfs.html Quote: The use of WAL mode largely obviates the need for this asynchronous I/O module. The WAL mode does not change the fact that these operations will still block the application process that embeds SQLite: - Slow read queries. - A large amount of writes. So would something like the original async module still be useful, even with WAL mode? Thanks. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] SQLITE_BUSY (SQLITE_BUSY_SNAPSHOT) due to a simple SELECT query -- Documentation needed!
You can also use “BEGIN EXCLUSIVE” before any writes; this gets you a write transaction immediately. If this returns OK all following read/writes will not return BUSY. A transaction can upgrade from a read only to a write (when you do a select followed by an insert). I think you get the snapshot error when the previous reads in the transaction no longer reference the HEAD database version because another connection has written to it. On Mon, 12 Aug 2019 at 11:03, Kira Backes wrote: > > So how do you propose to have consistency and isolation if SELECT does > not create an automatic transaction if no explicit transaction exists? > > I think this is a misunderstanding which might explain your first > email reply. I do not propose any change for sqlite, but I wasted 2 > days debugging and reading every page in sqlite documentation (locks, > transactions, isolation, error description) did not help me. So I just > want the documentation to be improved. I would change the error > description for SQLITE_BUSY_SNAPSHOT so that it no longer says "read > transaction" but instead says "read transaction or ongoing select > statement". And the first sentence in "transactions" should be changed > to indicated that select statement also create implicit read > transactions. > ___ > 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] How does errorLogCallback interrupt my running program?
By FFI I mean any of the public SQLite C functions exported from via FFI. Ok thanks, this makes sense the error callback is called whilst SQLite is executing exec/step. Do I need on callback per thread? On Sun, Aug 11, 2019 at 4:31 PM Clemens Ladisch wrote: > test user wrote: > > The error log takes a callback which is called when an error occurs from > > any SQLite FFI function: > > What exactly do you mean with "FFI"? You did not mention any other > language. > > > How does this interrupt my program whilst its running? > > As a library, SQLite is part of your program. It just calls the callback > while sqlite3_exec()/sqlite3_step() etc. is being executed. > > > Regards, > Clemens > ___ > 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
[sqlite] How does errorLogCallback interrupt my running program?
Hello, The error log takes a callback which is called when an error occurs from any SQLite FFI function: https://www.sqlite.org/errlog.html How does this interrupt my program whilst its running? If my program is busy, does it wait until the current function has completed and the process is idle? Also if I am using threads, do I need a callback handler per thread for all file connections on that thread? Or can I just use one error handler for all descendant threads of the root process? Thanks. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] THREADSAFE (1, SERIALIZED) vs (2, MULTITHREAD)
Quote: Please don't try to parallelize a part of your program which exists mostly to read or write to a SQLite database. SQLite is not a client/server system. Access to the database file on disk is a bottleneck. If you have 8 threads which spend most of their time executing SQLite calls, all they will do is constantly block one-another. I think this is true of writes. But I dont think it is true of reads. Given an immutable SQLite file (read only), this can be safely shared among different threads and processes. I think distributing reads among cores is in the class of problem called "embarrassingly parallel": https://en.wikipedia.org/wiki/Embarrassingly_parallel Quote: Access to the database file on disk is a bottleneck. https://www.amazon.com/Samsung-970-EVO-1TB-MZ-V7E1T0BW/dp/B07BN217QG/ This NVMe SSD disk has a read speed of 3GB/s. If I distribute readers across 8 CPU cores so they all compute concurrently (without blocking each other), and they all use a tiny portion of that disk read bandwidth, surely they would be 8x faster than using a single core? On Tue, Aug 6, 2019 at 1:42 PM Simon Slavin wrote: > On 6 Aug 2019, at 11:34am, test user wrote: > > > - Does the second request just take longer to return from the FFI call > whilst waiting for the mutex? > > Yes. You do not need to build backoff-and-retry code into your own > software. SQLite does it for you. For every database connection you open, > set a timeout using this: > > <https://sqlite.org/c3ref/busy_timeout.html> > > A setting of perhaps 30 seconds would be appropriate. The SQLite API will > use the amount of time you set to decide how long to keep retrying access > before it gives up, assumes that the database is permanently locked, and > returns an error code of SQLITE_BUSY. > > > So for example, if I had: > > > > - 8 cores > > - 8 threads > > - 8 db connections, 1 per thread > > - 1 database file > > - x amount of read requests per second > > > > If I were to load balance x requests over each of the 8 threads, all the > reads would complete concurrently when in SERIALIZED mode, with WAL enabled? > > Please don't try to parallelize a part of your program which exists mostly > to read or write to a SQLite database. SQLite is not a client/server > system. Access to the database file on disk is a bottleneck. If you have > 8 threads which spend most of their time executing SQLite calls, all they > will do is constantly block one-another. You will get a far simpler > program, far simpler to debug, if you do all your access through one thread > using one connection. > > What you /might/ want to do is have one thread which reads data from the > database and keeps an in-memory buffer full of, say, the next thousand rows > of data. Then other simultaneous processing threads can read their data > directly from memory. > > However, once again this may not be appropriate. You may find that your > software executes at acceptable speed without any parallelization at all. > I suggest you try this before devoting your time to complicated programming. > ___ > 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] THREADSAFE (1, SERIALIZED) vs (2, MULTITHREAD)
Thanks, When SQLITE_THREADSAFE=(1, SERIALIZED), how are the reads/writes queued? Is it per connection, file, or process? Quote: I don't know exactly what you mean by "how are read/writes queued". Quote: if you make two simultaneous calls on the same connection (from different threads, for example), one of them will get the mutex and proceed immediately, and the other one will have to wait until that mutex is released What I mean by "queue" is that if one request is waiting until a mutex is released, it is in an implicit queue for that resource. I wanted to know how this queue works: - Does the second request just take longer to return from the FFI call whilst waiting for the mutex? - Or does the FFI return SQLITE_BUSY and expect the caller to call again in the future? In other words: How should I handle this in my library that uses the FFI? So in summary, there is no difference in the multi threaded performance that can be gained between SERIALIZED and MULTITHREADED (aside from the mutex overhead)? The only difference is SERIALIZED enforces correct usage at a small overhead cost? So for example, if I had: - 8 cores - 8 threads - 8 db connections, 1 per thread - 1 database file - x amount of read requests per second If I were to load balance x requests over each of the 8 threads, all the reads would complete concurrently when in SERIALIZED mode, with WAL enabled? Assume other bottlenecks in the system are not an issue (like disk speed). Im just trying to confirm that SERIALIZED will not queue up requests for (1 file, multiple connections to that file, read only requests). On Tue, Aug 6, 2019 at 1:55 AM Keith Medcalf wrote: > > On Monday, 5 August, 2019 17:23, test user > wrote: > > >Whats the difference between these two options for the > >SQLITE_THREADSAFE compile time flag? > > >From the docs: > > >(1, SERIALIZED) > >(2, MULTITHREAD) > > The SQLite3 library code is not multiply re-entrant, but is only > singly-entrant on each connection. (It is, however, multiply entrant > provided that those entrances each are on a different connection) This is > because the SQLite3 connection pointer is a pointer to a structure that > contains information pertaining to that connection, and a statement (or > blob handle) is a sub-construct of the parent connection from which it was > made. Therefore, you may only make ONE CALL at a time into the SQLite3 > library per connection (or sub-construct thereof), because those calls will > mutate data associated with the connection. It does not matter from whence > that call originated (as in thread) merely that there can only be one > active at a time per connection. Period. > > The difference between SINGLETHREAD, SERIALIZED and MULTITHREAD is in how > this is guaranteed. > > When the THREADSAFE parameter is set to SEARALIZED this means that the > SQLite3 library itself will protect the connection data with a mutex to > make sure that you do not violate this constraint by "serializing" > simultaneous calls into the library on a single connection. This means > that if you make two simultaneous calls on the same connection (from > different threads, for example), one of them will get the mutex and proceed > immediately, and the other one will have to wait until that mutex is > released, thus ensuring that the single-entrance requirement is met. > > When the THREADSAFE parameter is set to MULTITHREAD this means that the > SQLite3 library WILL NOT protect the connection data with a mutex to > protect you from violating this constraint, and that it is entirely and > completely your responsibility to ensure that you do not violate the > single-entrance (per connection) requirement. If you do violate the single > entrance requirement, you may corrupt the database, the library, the > computer, the world, and cause the end of the universe. > > If and only if you are absolutely sure that you are complying with the > single-entrance requirement THEN you can change the THREADSAFE mode from > SERIALIZED to MULTITHREAD, which will save you a few nanoseconds per call > into the library because the mutexes will no longer be checked. > > When the THREADSAFE is set to either SERIALIZED or MULTITHREADED the code > to handle these mutexes is compiled into the library. You can switch > between these two modes at runtime. > > If you are only using one thread then you can set the THREADSAFE parameter > to SINGLETHREAD which will cause the mutex code to be omitted entirely, > saving you another nanosecond per call since you will not even need to > "jump around" the mutex checking code. > > When you compile the library with THREADSAFE set to SINGLETHREAD then the > mutex protection code IS NOT compiled into the library and therefore you
[sqlite] THREADSAFE (1, SERIALIZED) vs (2, MULTITHREAD)
Hello, Whats the difference between these two options for the SQLITE_THREADSAFE compile time flag? From the docs: (1, SERIALIZED) - Safe for use in a multithreaded environment - Enables all mutexes including the recursive mutexes on database connection and prepared statement objects. - The SQLite library will itself *serialize access to database connections* and prepared statements (2, MULTITHREAD) - Can be used in a multithreaded program so long as no two threads attempt to use the same database connection (or any prepared statements derived from that database connection) at the same time. When SQLITE_THREADSAFE=(1, SERIALIZED), how are the reads/writes queued? Is it per connection, file, or process? What happens when a request is in the queue, does it just wait until it can be actioned, or return SQLITE_BUSY? On a 8 core machine, how would I get optimal read throughput from a single database file? If I have many database connections to the same file, can they all read concurrently on all cores? Thanks, ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Explicit "read transaction" with journal_mode=WAL.
Quote: Connection 2 just happened to write lots of data and commit before connection 1 obtained a read transaction Quote: if SELECT on Connection 1 just happens to beat the write on Connection 2 - The order in the example is exact, not a guess what might happen. - Each step runs in a single thread on an event loop and is awaited, and assumed to return with OK. - The read transaction at the start is definite (supposing BEGIN READ existing). All I am saying is on "Connection 1: Get read transaction" could be: BEGIN READ NOT BEGIN; SELECT * FROM some_table; Quote: Why again do you care how BEGIN behaves Im just suggesting that if you can obtain a write transaction with a single trip over the FFI and an explicit command "BEGIN IMMEDIATE" the same could be true for "read transactions" I care because I think its a better API design which would then allow better higher level libraries. On Wed, Jul 31, 2019 at 9:46 PM Igor Tandetnik wrote: > On 7/31/2019 12:32 PM, test user wrote: > > In some runtimes, the scheduling of functions is unpredictable, so > although > > you will not have a `sleep 5` in the code, the runtime can produce this > > effect on loaded systems or with programs with long running sync > functions. > > > > > > An example of how you might use this: > > - Connection 1: Get a read transaction. > > > > - Connection 2: Get a write transaction, write a lot of data, commit. > > - Connection 2: SELECT report summary B. > > > > - Connection 1: SELECT report summary A, > > > > - Diff A and B to see what changed. > > Suppose you discovered that B and A are in fact the same. How do you know > whether that occurred because a) "get a read transaction" is "broken" in > that it doesn't actually acquire the lock as you expected, or because b) > Connection 2 just happened to write lots of data and commit before > connection 1 obtained a read transaction? > > In other words, in your example A == B is possible even if BEGIN worked > the way you expect it to work, and grabbed a read lock immediately. > Similarly, A != B is possible with BEGIN working the way it does now, if > SELECT on Connection 1 just happens to beat the write on Connection 2. It's > a matter of timing and scheduling, which you yourself posit is > unpredictable. > > So, since both A==B and A!=B are possible with either behavior of BEGIN, > why again do you care how BEGIN behaves? > -- > Igor Tandetnik > > ___ > 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] Explicit "read transaction" with journal_mode=WAL.
Quote: Why would that distinction matter to it in the first place?
So its clear in the API what lock you have at what time.
This would make predicting what happens in concurrent scenarios much easier.
An explicit "read transaction" is a single line of a program.
With an implicit "read transaction", you must know the semantics of SQLite
locking, and look to see where your first SELECT returns SQLITE_OK.
With read transaction:
```
c1.startRead((tx)=>{ // Issues a BEGIN READ
// X.
sleep(5 seconds);
// SELECT... still the same snapshot from point X being read.
});
```
*No* explicit read transaction:
```
c1.startRead((tx)=>{ // Just issues a normal BEGIN
// X.
sleep(5 seconds);
// SELECT What ever was written in the last 5 seconds will be in
the result set, which is unexpected.
});
```
With a explicit "read transaction" the programmer can assume that whenever
`startRead` returns to the runtime the snapshot is guaranteed (it does not
matter how long it takes to issue the first SELECT).
In some runtimes, the scheduling of functions is unpredictable, so although
you will not have a `sleep 5` in the code, the runtime can produce this
effect on loaded systems or with programs with long running sync functions.
An example of how you might use this:
- Connection 1: Get a read transaction.
- Connection 2: Get a write transaction, write a lot of data, commit.
- Connection 2: SELECT report summary B.
- Connection 1: SELECT report summary A,
- Diff A and B to see what changed.
On Wed, Jul 31, 2019 at 3:24 PM Igor Tandetnik wrote:
> On 7/31/2019 6:36 AM, test user wrote:
> > As an example, the client of this library could:
> >
> > - A. Obtain a "read transaction", *without running any SELECTs*.
> > - B. Complete 20 write transactions in another process.
> > - C. Begin reading from the read transaction (A) at the point before the
> > transactions had occurred.
>
> In the current world, that client would execute BEGIN, then wait a bit,
> then start reading and discover the data written by another process. How
> does it know that those writes occurred between BEGIN and SELECT, and not
> before BEGIN? Why would that distinction matter to it in the first place?
>
> Do you envision some other channel of communication and synchronization
> between these two processes, outside the SQLite database, that would help
> establish that writes occurred after BEGIN? With SQLite alone, it's
> impossible to tell whether the sequence of events was A-B-C or B-A-C - both
> sequences produce the exact same observable behavior. So guarding against B
> squeezing between A and C seems rather pointless.
> --
> Igor Tandetnik
>
> ___
> 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] [SPAM?] Re: Explicit "read transaction" with journal_mode=WAL.
Yeh I think this is the way to go currently. I just wanted to be sure I understood it correctly and that it doesn’t exist already. Is there a standard place where people can request features to be added to SQLite? The Fossil repo perhaps? On Wed, 31 Jul 2019 at 12:53, Richard Damon wrote: > As has been pointed out, your function that is called can do the BEGIN > and then a SELECT that hits the database to force the obtaining of the > read lock. The fact that the BEGIN didn't get the lock is then not seen > by the users of your API. > > IF at some point a new BEGIN SHARED IMMEDIATE operation becomes > available, you can change you function's internals, and the caller > doesn't know the difference except that the call got perhaps a bit faster. > > On 7/31/19 6:36 AM, test user wrote: > > Quote: What importance does it have for you that it already holds an > > "end-mark? > > Quote: Why would it matter that a writer did write and commit between the > > "reader" BEGIN and its first read? > > > > Im writing a library and would like to have an API where the "read > > transaction" has a clear beginning in time. > > > > BEGIN IMMEDIATE forces a "write transaction", but there is no counterpart > > for a "read transaction". > > > > As an example, the client of this library could: > > > > - A. Obtain a "read transaction", *without running any SELECTs*. > > - B. Complete 20 write transactions in another process. > > - C. Begin reading from the read transaction (A) at the point before the > > transactions had occurred. > > > > > > At the moment, a "read transaction" is only started on the first SELECT. > > > > If a client tries to start a "read transaction" with BEGIN, and that > > returns SQLITE_OK, its not clear that this has not actually begun any > > transaction until the first SELECT query. > > > > This would enable an API like: > > > > const r = await db.startReadTx(); > > const w = await db.startWriteTx(); > > > > // At this point in the runtime it clear when the transactions have > begun, > > and how they will impact other concurrent read/write transactions. > > > > > > > > On Tue, Jul 30, 2019 at 11:40 PM Olivier Mascia wrote: > > > >>> Le 31 juil. 2019 à 00:22, Keith Medcalf a écrit > : > >>> > >>> I can see where a BEGIN IMMEDIATE SHARED would be useful in non-WAL > mode > >> though. I will grant that there may be cases where it might be useful > in > >> WAL mode, even though I cannot think of any. > >> > >> Fully agree. > >> > >> — > >> Best Regards, Meilleures salutations, Met vriendelijke groeten, Mit > besten > >> Grüßen, > >> Olivier Mascia > >> > >> ___ > >> 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 > > > -- > Richard Damon > > ___ > 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] Explicit "read transaction" with journal_mode=WAL.
Quote: What importance does it have for you that it already holds an "end-mark? Quote: Why would it matter that a writer did write and commit between the "reader" BEGIN and its first read? Im writing a library and would like to have an API where the "read transaction" has a clear beginning in time. BEGIN IMMEDIATE forces a "write transaction", but there is no counterpart for a "read transaction". As an example, the client of this library could: - A. Obtain a "read transaction", *without running any SELECTs*. - B. Complete 20 write transactions in another process. - C. Begin reading from the read transaction (A) at the point before the transactions had occurred. At the moment, a "read transaction" is only started on the first SELECT. If a client tries to start a "read transaction" with BEGIN, and that returns SQLITE_OK, its not clear that this has not actually begun any transaction until the first SELECT query. This would enable an API like: const r = await db.startReadTx(); const w = await db.startWriteTx(); // At this point in the runtime it clear when the transactions have begun, and how they will impact other concurrent read/write transactions. On Tue, Jul 30, 2019 at 11:40 PM Olivier Mascia wrote: > > Le 31 juil. 2019 à 00:22, Keith Medcalf a écrit : > > > > I can see where a BEGIN IMMEDIATE SHARED would be useful in non-WAL mode > though. I will grant that there may be cases where it might be useful in > WAL mode, even though I cannot think of any. > > Fully agree. > > — > Best Regards, Meilleures salutations, Met vriendelijke groeten, Mit besten > Grüßen, > Olivier Mascia > > ___ > 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] Explicit "read transaction" with journal_mode=WAL.
The docs do not mention that it does not apply in WAL mode: https://sqlite.org/lang_transaction.html#immediate - "After a BEGIN IMMEDIATE, no other database connection will be able to write to the database" I tested it out against the API with WAL mode enabled, it seems a "BEGIN IMMEDIATE" will block other writers, so it cannot be used as a "read transaction": ``` run(A, "PRAGMA journal_mode=WAL"); run(B, "PRAGMA journal_mode=WAL"); run(A, "CREATE TABLE t1(a PRIMARY KEY, b);"); run(B, "BEGIN IMMEDIATE"); run(A, "BEGIN"); run(A, "INSERT INTO t1 (a, b VALUES (1, 2), (3, 4)")); // SQLITE_BUSY run(A, "INSERT INTO t1 (a, b VALUES (5, 6), (7, 8)")); run(A, "COMMIT"); run(B, "SELECT * FROM t1"); ``` I could of made a mistake though. Do you have an example/docs reference? BEGIN READ is semantically what I was trying to describe as what I was trying to achieve, I understand its not in the language! On Tue, Jul 30, 2019 at 9:43 PM Simon Slavin wrote: > On 30 Jul 2019, at 9:39pm, test user wrote: > > > BEGIN IMMEDIATE will start a write transaction, which will block other > writers with SQLITE_BUSY until its complete. > > This does not apply to WAL mode. You wrote that you were using WAL mode. > > > What I would like is something like BEGIN READ > > The statement 'BEGIN READ' does not exist in SQlite. > > <https://www.sqlite.org/lang_transaction.html> > > Simon. > ___ > 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] Explicit "read transaction" with journal_mode=WAL.
Quote: "This is the effect if you use BEGIN IMMEDIATE instead of just BEGIN" BEGIN IMMEDIATE will start a write transaction, which will block other writers with SQLITE_BUSY until its complete. What I would like is something like BEGIN READ, which will not block writers for its duration. This "read transaction" can see all committed transactions that happened before it, but none after it. At the moment it seems to get this guarantee I will need to do a "BEGIN; SELECT * from sqlite_master LIMIT 1" On Tue, Jul 30, 2019 at 8:23 PM Simon Slavin wrote: > On 30 Jul 2019, at 6:44pm, test user wrote: > > > I am using `journal_mode=WAL`. > > > > What I am trying to do: > > > > From the first `BEGIN` that returns `SQLITE_OK`, all SELECTs read from > the same snapshot/point in time. > > This is the effect if you use BEGIN IMMEDIATE instead of just BEGIN. So > do BEGIN IMMEDIATE, then as many SELECTs as you want, then END. For the > duration of that transaction, all your SELECTs will reflect the same > snapshot of the database. > > > The issue is that its hard to tell if I reading from a read snapshot > (where any successful commit on other connections since the reads BEGIN are > ignored). > > Depending on various things, other connections trying to change the > database will be blocked (locked out of making changes), or will make > changes that will not be 'seen' by the above transaction. > ___ > 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] Explicit "read transaction" with journal_mode=WAL.
Thanks David, `SELECT 1` = rows 0 was a mistake in the example. How sure are you that "any SELECT that reads from the DB file starts a read transaction"? Does the read transaction read from a snapshot of the entire DB, or are only specific tables in the read snapshot? On Tue, Jul 30, 2019 at 7:14 PM David Raymond wrote: > To get the read lock you're going to need to read something from the > database file. > > I think this page is your best bet: > https://www.sqlite.org/lang_transaction.html > > "Transactions can be deferred, immediate, or exclusive. The default > transaction behavior is deferred. Deferred means that no locks are acquired > on the database until the database is first accessed. 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..." > > So after a "begin deferred" you have to actually do something that > requires file access in order to get the shared lock/start your read > snapshot on the file. So if you want to get that shared lock/read snapshot > you can always do something like a select from sqlite_master. "select 1;" > doesn't need to access the file to complete, so it doesn't take out the > shared lock (though it should be returning 1 row, are you sure it's 0?) > > > > -Original Message- > From: sqlite-users On > Behalf Of test user > Sent: Tuesday, July 30, 2019 1:45 PM > To: SQLite mailing list > Subject: [sqlite] Explicit "read transaction" with journal_mode=WAL. > > Hello, > > How can I start a "read transaction" from BEGIN? > > > I am using `journal_mode=WAL`. > > What I am trying to do: > > From the first `BEGIN` that returns `SQLITE_OK`, all SELECTs read from the > same snapshot/point in time. > > > The issue is that its hard to tell if I reading from a read snapshot (where > any successful commit on other connections since the reads BEGIN are > ignored). > > When is a read transaction started? > > > As an example, connection A and B: > > ``` > run(A, "CREATE TABLE t1(a PRIMARY KEY, b);"); > > run(B, "BEGIN"); > run(B, "SELECT * FROM t1"); = 0 rows > > run(A, "BEGIN"); > run(A, "INSERT INTO t1 (a, b) VALUES (1, 2), (3, 4)"); > run(A, "INSERT INTO t1 (a, b) VALUES (5, 6), (7, 8)"); > run(A, "COMMIT"); > > run(B, "SELECT * FROM t1"); = 0 rows; READ TRANSACTION started > ``` > > ``` > run(A, "CREATE TABLE t1(a PRIMARY KEY, b);"); > > run(B, "BEGIN"); > run(B, "SELECT 1"); = 0 rows > > run(A, "BEGIN"); > run(A, "INSERT INTO t1 (a, b) VALUES (1, 2), (3, 4)"); > run(A, "INSERT INTO t1 (a, b) VALUES (5, 6), (7, 8)"); > run(A, "COMMIT"); > > run(B, "SELECT * FROM t1"); = 4 rows; READ TRANSACTION *NOT* started > ``` > > > ``` > run(A, "CREATE TABLE t1(a PRIMARY KEY, b);"); > > run(B, "BEGIN"); > > > run(A, "BEGIN"); > run(A, "INSERT INTO t1 (a, b) VALUES (1, 2), (3, 4)"); > run(A, "INSERT INTO t1 (a, b) VALUES (5, 6), (7, 8)"); > run(A, "COMMIT"); > > run(B, "SELECT * FROM t1"); = 4 rows; READ TRANSACTION *NOT* started > ``` > > > > https://www.sqlite.org/isolation.html > > Quote: "BEGIN IMMEDIATE command goes ahead and starts a write transaction" > > This is the only page where I can find a mention of the idea of "read > transaction" and "write transaction". > > > BEGIN IMMEDIATE allows the explicit start of a "write transaction". > > Does an API exist for a "read transaction"? > > Thanks > ___ > 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 > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Explicit "read transaction" with journal_mode=WAL.
Hello, How can I start a "read transaction" from BEGIN? I am using `journal_mode=WAL`. What I am trying to do: From the first `BEGIN` that returns `SQLITE_OK`, all SELECTs read from the same snapshot/point in time. The issue is that its hard to tell if I reading from a read snapshot (where any successful commit on other connections since the reads BEGIN are ignored). When is a read transaction started? As an example, connection A and B: ``` run(A, "CREATE TABLE t1(a PRIMARY KEY, b);"); run(B, "BEGIN"); run(B, "SELECT * FROM t1"); = 0 rows run(A, "BEGIN"); run(A, "INSERT INTO t1 (a, b) VALUES (1, 2), (3, 4)"); run(A, "INSERT INTO t1 (a, b) VALUES (5, 6), (7, 8)"); run(A, "COMMIT"); run(B, "SELECT * FROM t1"); = 0 rows; READ TRANSACTION started ``` ``` run(A, "CREATE TABLE t1(a PRIMARY KEY, b);"); run(B, "BEGIN"); run(B, "SELECT 1"); = 0 rows run(A, "BEGIN"); run(A, "INSERT INTO t1 (a, b) VALUES (1, 2), (3, 4)"); run(A, "INSERT INTO t1 (a, b) VALUES (5, 6), (7, 8)"); run(A, "COMMIT"); run(B, "SELECT * FROM t1"); = 4 rows; READ TRANSACTION *NOT* started ``` ``` run(A, "CREATE TABLE t1(a PRIMARY KEY, b);"); run(B, "BEGIN"); run(A, "BEGIN"); run(A, "INSERT INTO t1 (a, b) VALUES (1, 2), (3, 4)"); run(A, "INSERT INTO t1 (a, b) VALUES (5, 6), (7, 8)"); run(A, "COMMIT"); run(B, "SELECT * FROM t1"); = 4 rows; READ TRANSACTION *NOT* started ``` https://www.sqlite.org/isolation.html Quote: "BEGIN IMMEDIATE command goes ahead and starts a write transaction" This is the only page where I can find a mention of the idea of "read transaction" and "write transaction". BEGIN IMMEDIATE allows the explicit start of a "write transaction". Does an API exist for a "read transaction"? Thanks ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Determining valid statement placeholders.
Thanks Keith, I think you are right.
I can enforce only using index-based or key-based placeholders and force
the user to supply data as an array (indexed) or an object (keyed).
I think I was assuming I would allow treating index-based placeholders as
keys {"?10": "data"}, which is where the "detect valid placeholders" need
came from.
Thanks for the help.
On Mon, Jul 22, 2019 at 2:23 PM Keith Medcalf wrote:
>
> I don't see what is so hard. APSW does it:
>
> >python
> Python 2.7.16 (v2.7.16:413a49145e, Mar 4 2019, 01:30:55) [MSC v.1500 32
> bit (Intel)] on win32
> Type "help", "copyright", "credits" or "license" for more information.
> >>> import apsw
> >>> db = apsw.Connection('')
> >>> db.execute('select ?, ?10;', ('one', 'ten')).fetchone()
> Traceback (most recent call last):
> File "", line 1, in
> File "C:\python\lib\site-packages\Local\newapsw.py", line 39, in execute
> return self.cursor().execute(*args, **kwargs)
> apsw.BindingsError: Incorrect number of bindings supplied. The current
> statement uses 10 and there are 2 supplied. Current offset is 0
> >>>
>
> The statement required 10 parameters and only 2 were supplied. ERROR!
>
> Internally it uses sqlite3_bind_parameter_count to find out how many
> parameters need binding and requires that the number of "parameters" passed
> match the number of parameters "expected" when binding positionally.
>
> >>> db.execute('select :1, :10;', {'2': 'one', '10': 'ten'}).fetchone()
> Row(_0=None, _1=u'ten')
> >>> db.execute('select :1, :10;', {'1': 'one', '10': 'ten'}).fetchone()
> Row(_0=u'one', _1=u'ten')
> >>>
>
> When binding by name, it looks up the names in the provided dictionary and
> binds those it finds.
>
> You can also bind named parameters to a positional list (in which case it
> is the programmers job to keep track of what they are doing) since a named
> parameter is merely syntactic sugar on top of positional parameters:
>
> >>> db.execute('select :1, :10;', ('one', 'two')).fetchone()
> Row(_0=u'one', _1=u'two')
> >>>
>
>
>
> It is not very difficult. You call sqlite3_bind_parameter_count. Then if
> you are binding positionally you make sure there are sufficient positional
> parameters provided to bind them all. It you are binding by name, you
> cycle through the parameters, get the name, and then bind the given named
> parameter to that parameter. apsw chooses to ignore missing items when
> binding by name -- sounds like you simply want to ERROR instead ...
>
> The only issue I can see is that when requesting the name of an positional
> parameter that has no name it returns null rather than the positional name,
> however, this is pretty easy to work around in pretty much any programming
> language ... that is if sqlite3_parameter_name(stmt, x) return null then
> the name is a ? followed by x in decimal (sprintf("?%d", x) or thereabouts
> having appropriate accomodations for the language syntax and buffer safety,
> etc)
>
> However, you cannot bind to positional parameters by name:
>
> >>> db.execute('select ?1, ?10;', {'2': 'one', '10': 'ten'}).fetchone()
> Traceback (most recent call last):
> File "", line 1, in
> File "C:\python\lib\site-packages\Local\newapsw.py", line 39, in execute
> return self.cursor().execute(*args, **kwargs)
> apsw.BindingsError: Binding 1 has no name, but you supplied a dict (which
> only has names).
>
>
> *the builtin python sqlite3 wrapper does the same thing I expect (though I
> have never actually looked) but it is somewhat braindead otherwise, so I
> don't use it and haven't bothered to test what it does in these
> circumstances. Though my expectation is that it would behave somewhat
> similarly.
> --
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
> >-Original Message-
> >From: sqlite-users [mailto:sqlite-users-
> >boun...@mailinglists.sqlite.org] On Behalf Of test user
> >Sent: Monday, 22 July, 2019 06:36
> >To: SQLite mailing list
> >Subject: Re: [sqlite] [EXTERNAL] Determining valid statement
> >placeholders.
> >
> >I understand the problem, but I want my library to be able to detect
> >the
> >problem programatically.
> >
> >Currently if a user mixes index-based and key-based placeholders, the
> >only
> >thing a library using SQLite can do us run the query with unbound
> >placeholders set to null (the SQLite default).
>
Re: [sqlite] [EXTERNAL] Determining valid statement placeholders.
I understand the problem, but I want my library to be able to detect the
problem programatically.
Currently if a user mixes index-based and key-based placeholders, the only
thing a library using SQLite can do us run the query with unbound
placeholders set to null (the SQLite default).
Id like the ability to instead throw an error in this case.
I think SQLite internally knows how many placeholders are in the query at
parse time.
My question is how can I get the data via the API, or if it would be
considered to add a function to get this data?
On Mon, Jul 22, 2019 at 12:29 PM Keith Medcalf wrote:
>
> On Monday, 22 July, 2019 04:34, Enzo wrote:
>
> >It is not the same information.
>
> >I want to be able to determine "has the user bound all placeholder
> >values with data?".
>
> >The user provides this as input:
>
> >query="SELECT ?, ?10"
> >data={"0": "data-a", "10": "data-b"}
>
> >Note: This IS valid, as they have provided all data for placeholders.
>
> >Using the APIs you mentioned:
>
> >count=10
> >name(10) = "?10"
>
> >So according to the API, the indexes [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]
> >are placeholders in the query.
>
> >As you can see, only indexes [1, 10] are valid placeholders in the
> >query.
>
> >So, as far as I can tell, it is not possible to get this from the
> >API:
>
> >query="SELECT ?, ?10"
> >valid_placeholder_indexes=[1, 10]
>
> >It is only possible to get this:
>
> >query="SELECT ?, ?10"
> >maybe_placeholder_indexes=[1, 2, 3, 4, 5, 6, 7, 8, 9, 10]
>
> The fundamental issue is that you are confusing POSITIONAL parameters with
> NAMED parameters. ?nnn where nnn is numeric describe POSITIONAL
> parameters, so in the query SELECT ?, ?10 you are using positional
> parameter 1 and positional parameter 10. That implies that there are 10
> positional parameters. The fact that you are only using those positional
> parameters sparsely (only using positional parameter 1 and positional
> parameter 10) does not mean that you are using two parameters, it means
> that you are using 10, but only referencing 2 of them.
>
> Said another way, you are declaring a function that looks like this:
>
> int stmt(a, b, c, d, e, f, g, h, i, j)
>return a + j
>
> and expecting to be able to call it as
>
> z = stmt(1, 6)
>
> expecting some magic to know that the second parameter is really parameter
> 10.
>
> https://www.sqlite.org/lang_expr.html#varparam
>
> There may be many positional parameters (like 999 in the default build)
> and sqlite3_bind_parameter_count returns the "index" of the greatest
> parameter number used in the statement. Having created 10 parameters
> sqlite has absolutely no clue that you happen to be using only parameter 1
> and parameter 10. If you only needed 2 parameters you should have only
> created 2, not 10. That is what NAMED parameters are for.
>
> If you change from using positional (?) parameters to using named (:) or
> (@) what happens?
>
> select :1, :10; should only create 2 parameters named :1 and :10 ...
>
> --
> 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] [EXTERNAL] Determining valid statement placeholders.
Simon Slavin: "Please don't use numbers as names."
The manual states:
- "?NNN": where N is an *integer*.
- "?": Programmers are encouraged to use one of the symbolic formats below
or the ?NNN format above instead."
The use case:
I am creating a library that takes SQL from the application and runs it via
the SQLite FFI.
I want to be able to write this function:
if has_bound_all_placeholders_in_query_string() {
// run query
} else {
throw Error("You must bind all query parameters.")
// This branch cannot be created with the current SQLite FFI APIs.
// It is useful because the SQLite default is to bind NULL to unbound
placeholders, which may not be what the user wanted. If they wanted this
they would of just used the constant NULL instead of a placeholder.
// In dynamic languages where both the query string and the data could
be dynamically generated, being able to enforce binding all placeholders is
useful for correctness.
}
A new function could be added:
For query string "SELECT ?, ?10"
`sqlite3_bind_parameter_valid_indexes() -> [1, 10]`
How can I propose adding this?
On Mon, Jul 22, 2019 at 6:52 AM Hick Gunter wrote:
> No. As the creator of a statement, you are expected to know either the
> position or the name of any SQL parameters contained therein.
> Determining which indexes are legal does not help in determining what has
> been bound, not even if anything has been bound. Please note that
> sqlite3_reset() does not clear the bindings.
>
> What is the underlying use case?
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> Im Auftrag von test user
> Gesendet: Sonntag, 21. Juli 2019 21:04
> An: sqlite-users@mailinglists.sqlite.org
> Betreff: [EXTERNAL] [sqlite] Determining valid statement placeholders.
>
> Is there a `sqlite3` C FFI API that allows me to determine which index
> values are valid to bind to?
>
> The `sqlite3_bind_parameter_*` APIs (count, name, index) do not actually
> provide the valid indexes.
>
> For example: "?, ?10" would have two valid indexes: (1, 10).
>
> But how can I know that indexes 2-9 are invalid from the API?
>
> I have tried binding null to 2-9, but get SQLITE_OK.
>
> I want to be able to check clients have provided all data needed for a
> query instead having SQLite bind null by default for placeholders with no
> data binding.
>
> Thanks.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
> Gunter Hick | Software Engineer | Scientific Games International GmbH |
> Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O)
> +43 1 80100 - 0
>
> May be privileged. May be confidential. Please delete if not the addressee.
> ___
> 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
[sqlite] Determining valid statement placeholders.
Is there a `sqlite3` C FFI API that allows me to determine which index values are valid to bind to? The `sqlite3_bind_parameter_*` APIs (count, name, index) do not actually provide the valid indexes. For example: "?, ?10" would have two valid indexes: (1, 10). But how can I know that indexes 2-9 are invalid from the API? I have tried binding null to 2-9, but get SQLITE_OK. I want to be able to check clients have provided all data needed for a query instead having SQLite bind null by default for placeholders with no data binding. Thanks. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
