Lazarus Under The Hood

[Reney Shammo]

Inmy professional life I've worked mainly on mainframe DB2 (a very different beast to sqlite and possibly causing unjustified assumptions on my part). I have coded Delphi programs on Windows using the BDE and Absolute database.I recently started exploring Lazarus and sqlite using Zeos.

This all leaves me very puzzled. The documentation I could find says that you get locking when two processes try and access the database at the same time. But my code is very simple, no multithreading, and I issue a Close on the read SQL before trying the insert. The database is on the C: drive so there's no NAS concurrency issues. I've tried adding StartTransaction and Commit commands around both the SELECT and INSERT statements but to no avail.

I'm very familiar with FPC and lazarus, but not with Zeos, so not sure what it does under the hood. Just musing from my experience, in SQLite, you need to finish a transaction or close a current query before starting the next. (A query which is not opened within an explicit transaction is a transaction unto itself).

As an aside - I use two distinct ways of accessing SQLite DBs in FPC, the first is the bog standard TSQLQuery tooling, works a treat (and the same way as MySQL, Postgres, MSSQL, etc. variations). I can give soem examples of this if needed.

The second method is a custom-made wrapper of the SQLite library (which works on any platform) and is quite fast and versatile. You can see it at work in the SQLiteSpeed tool and I'm willing to share it if you contact me directly on ryansmithhe at gmail dot com.

Hi Ryan,Yes, I make sure I close any query after I'm finished with it. And in the program's current state I only have one query open at a time. I would have thought that closing a query would release any locks associated with the query but I'm not really sure. I have a good idea of how locking works under DB2 but despite the most monk like study of the sqlite documentation I still cannot wrap my head around the way sqlite does locking. Locking a database is a meaningless idea under DB2 - the largest object you can lock there is a table (technically a tablespace)I can try wrapping every SQL statement in a transaction and issuing a commit after every close but this is just flailing around - I don't really understand what's going on.I can try recoding the program not to use Zeos but I really like to get an understanding of just what the issue is.

Locking in SQLite works spectacularly easy and is not usually a problem, which is why I was suggesting that maybe Zeos is the problem, or you are not closing the query (which you confirmed you did indeed do). Further tot his you report seeing "Database Locked" errors, which is not really something SQLite would say (it has a "Database Busy" error) which makes me think that Zeos may be doing some other locking on top of the DB or such.

The reason locking seems weird is that it very much depends on the journal mode. I'll try explain it in broad non-technical terms for the basic journal modes, but note that WAL mode works a bit different.

If a transaction (or even single statement) executes, it has to ensure the statement sees a consistent view of the data for the duration, so it typically acquires a read-lock on the DB (how exactly this happens depends very much on the underlying Virtual File-System interface, or VFS for short). Once a read-lock is held, other queries can be started that also read the data, but not one that wishes to write data (such a request will get the "Database Busy" error). If the same transaction (which holds the read-lock) wishes to start writing, it should be able to upgrade to a write-lock once other readers have completed, before which the error will also be returned, and after which, other readers have to wait, and if they do try to start a read operation at this point, will get the "Database Busy" error. (Again, WAL mode works different and allows concurrent readers while writing, and some of the above workings can be adjusted with pragmas).

It is thus imperative for both read and write transactions to finish as fast as possible and when mixing and matching from different connections, to expect the occasional "Busy" signal back.Also, there is nothing wrong with getting a Busy signal back, it's merely a state and you should be handling it by simply waiting some time and trying again.

You could also have SQLite do that automagically by specifying a Busy-timeout which would simply make it, when encountering a "Busy" signal, wait a bit in progressively larger increments until no longer busy, or until the timeout milliseconds expire, whichever happens first. It's not uncommon to set timeouts of one or two minutes for big DBs.

Lastly - NONE of the above matters if the DB enters a permanent locked state, which is usually the result of programmer error, not closing a statement, or encountering a real dead-lock (which is hard to engineer, but can happen). That is why our first responses were along the lines of "Are you sure you close the statements?"

I'll also mention that you can search the forums and check, but nobody really battles with DB locking errors (unless OS induced), it just works well, so to encounter someone who has trouble with it, one has to assume a fundamental mistake. The biggest trouble is that seemingly none of us know Zeos well - it may also be useful to ask this on the Lazarus forum.

Hi Ryan,I'm puzzled because the program is very simple and no one else seems to be getting this sort of error.I will try doing a commit after every database read or update SQL and see if that makes a difference (although I would have thought that close on a SQL query would remove all the locks the query had acquired).Failing that, I'll try recoding the program not to use Zeos and see how that goes. I wanted to try Zeos because it emulates the old Borland BDE but documentation on Zeos is very hard to find.I'll report back in a few days.Thank you for your help.

Ok, program is working now. Apparently I needed to add a Commit after every SQL close. I'm a bit puzzled why this should be necessary but at least we can move forward.Thanks to everyone who offered help.

I am also working on Lazarus and accessing SQLite database. I am using the provided TSQLite3Connection. I would appreciate having some examples as you mention so that I can see whether I'm doing things right. Especially error handling (retrying a busy state) would be of interest to me.

Well, that was just a thought, since it's the only problem I remember while using zeos with sqlite.Did you try closing the TZConnection and restarting Lazarus? Or playing with the DesignConnection property (I never remember if it should be true or false)?.Also check the TransactionIsolationLevel, by default is tiNone so it shouldn't keep the database locked (unless you manually start a transaction IIRC).

Jason Peter Todd is a character appearing in American comic books published by DC Comics. First appearing in Batman #357 in March 1983,[3] he was created to succeed Dick Grayson as Robin, Batman's partner and sidekick. He initially shared a similar origin to Grayson, being the son of circus acrobats who are killed by criminals in Gotham (Dick's were killed by a local mob boss who sabotaged their trapeze while Jason's parents were killed by Killer Croc) and adopted by Bruce Wayne/Batman as his son and protege. Following the Crisis on Infinite Earths event and the rebooting of DC's main comics universe, Jason's origin was changed to being a pre-teen street urchin and petty thief who Bruce adopted and took under his wing after finding the boy attempting to steal the tires off of the Batmobile. This origin has since become the standard for subsequent iterations of the character.

Todd has made several appearances as Robin and Red Hood in other forms of media outside of comics, including television series, films, and video games. The 2015 game Batman: Arkham Knight in particular reimagined Todd resurfacing with a new villain identity, the Arkham Knight, following 10 years trapped in Arkham Asylum and tortured by The Joker who conditioned him to despise and turn on his former mentor before assuming the Red Hood identity near the end of the game.

In 1988, Dennis O'Neil suggested that an audience might be attracted to the comics by being offered the opportunity to influence the creative process.[7] Settling on the idea of the telephone poll via a 1-900 number, O'Neil had decided due to discussions with DC Comics president Jenette Kahn that the poll should not be wasted on something insignificant. O'Neil settled on using the poll to determine the fate of the second Robin. O'Neil said, "The logical candidate was Jason because we had reason to believe that he wasn't that popular anyway. It was a big enough stunt that we couldn't do it with a minor character."[8] Even though Jason Todd was unpopular with readers, O'Neil could not decide what to do with the character, so he opted to present the choice to the readership.[7]

Despite the poll results, O'Neil noted, "We did the deed, and we got a blast of hate mail and a blast of negative commentary in the press."[12] A few comics creators voiced their displeasure at the event. Writer/artist Frank Miller, who had worked on Batman: The Dark Knight Returns and Batman: Year One, said, "To me, the whole killing of Robin thing was probably the ugliest thing I've seen in comics, and the most cynical."[13] However, DC stood behind the outcome of the poll. O'Neil was quoted on the back cover of A Death in the Family trade paperback collecting the story with Todd's death as saying, "It would be a really sleazy stunt to bring him back."[14] O'Neil would later regret his comment.[15]

There was a degree of discontinuity between the Batman and Detective Comics titles with regards to the portrayal of Jason. A lot of adventures occurred post-Crisis which fit with the circus acrobat era and in some cases ran simultaneously in Detective as the street kid origin was being laid out in Batman. This led to a blackout of almost any Robin appearances in Detective. This became especially apparent after his death. Eleven months passed between Jason's death in Batman #428 and the first mention of his passing in Detective Comics #606.

3a8082e126