Re: [web2py] Re: database locking web2py vs. "external" access...

On Thu, Jun 28, 2012 at 8:04 AM, Anthony wrote: >> > Anyway, you might be better off looking into using the scheduler, or a >> > third-party task queue like Celery (a web2py Celery plugin was started >> > at >> > some point, though not sure it is complete). >> >> I don't understand, how will that

Re: [web2py] Re: database locking web2py vs. "external" access...

> > > Anyway, you might be better off looking into using the scheduler, or a > > third-party task queue like Celery (a web2py Celery plugin was started > at > > some point, though not sure it is complete). > > I don't understand, how will that let me do a read-modify-update > transaction? It

Re: [web2py] Re: database locking web2py vs. "external" access...

On Thu, Jun 28, 2012 at 1:02 AM, Jonathan Lundell wrote: > The SQLite adapter simulates select for update by wrapping the select+update > in a transaction. Not too efficient, since it has to lock the entire > database, but it'll do it. Thanks, I wish it worked in the released version, and I'm hop

Re: [web2py] Re: database locking web2py vs. "external" access...

On Thu, Jun 28, 2012 at 12:08 AM, Anthony wrote: > SQLite is a file-based database, so has to have more course locking than a > typical client/server database. Other databases have "select for update" > functionality, which allows you to select a set of records and lock them. > web2py actually sup

Re: [web2py] Re: database locking web2py vs. "external" access...

This is all a great unearthing of the Mystery of Transactions. Thanks for the investigation, Doug. This was difficult for me to learn when I got into web2py as well. Perhaps we could write up all this knowledge somewhere, now that you're figuring it out? Can we have a section on Transactions i

Re: [web2py] Re: database locking web2py vs. "external" access...

On Jun 27, 2012, at 9:08 PM, Anthony wrote: > SQLite is a file-based database, so has to have more course locking than a > typical client/server database. Other databases have "select for update" > functionality, which allows you to select a set of records and lock them. > web2py actually suppor

Re: [web2py] Re: database locking web2py vs. "external" access...

SQLite is a file-based database, so has to have more course locking than a typical client/server database. Other databases have "select for update" functionality, which allows you to select a set of records and lock them. web2py actually supports this, via .select(..., for_update=True). Anyway,

Re: [web2py] Re: database locking web2py vs. "external" access...

On Wed, Jun 27, 2012 at 11:53 AM, Anthony wrote: >> > Yes, it does release the lock, as soon as the select is complete. You do >> > not >> > have to do db.commit() after a select. >> Please show me where that is documented. >> Section 7 of http://www.sqlite.org/lockingv3.html says that ONLY >> hap

Re: [web2py] Re: database locking web2py vs. "external" access...

> > > Yes, it does release the lock, as soon as the select is complete. You do > not > > have to do db.commit() after a select. > Please show me where that is documented. > Section 7 of http://www.sqlite.org/lockingv3.html says that ONLY > happens when autocommit is on, and you have stated th

Re: [web2py] Re: database locking web2py vs. "external" access...

On Wed, Jun 27, 2012 at 10:13 AM, Anthony wrote: >> Again, with autocommit off: >> The server process does a query/select to see if there is any work to >> do. This provokes SQLite to acquire a SHARED lock. Then the server >> process decides "oops, no work to do, let's go to sleep for a minute". >

Re: [web2py] Re: database locking web2py vs. "external" access...

Some nice assumptions here... are you actually suggesting that: - could ever be a stable release of web2py out there that will hold the lock for any pending select ? - the book suggesting a good behaviour is absolutely wrong ? Web2py developers and testers in general would spot that in a breeze w

Re: [web2py] Re: database locking web2py vs. "external" access...

> > Again, with autocommit off: > The server process does a query/select to see if there is any work to > do. This provokes SQLite to acquire a SHARED lock. Then the server > process decides "oops, no work to do, let's go to sleep for a minute". > Notice that it does nothing to release that lo

Re: [web2py] Re: database locking web2py vs. "external" access...

> > Well, earlier it was claimed that the DAL for SQLite left autocommit > on, A quote from my earlier response: *web2py is just using the Python standard library sqlite3 module. According to the docs, it looks l

Re: [web2py] Re: database locking web2py vs. "external" access...

On Sat, Jun 23, 2012 at 1:24 PM, Anthony wrote: > No, when autocommit is disabled, that doesn't immediately result in a shared > lock being acquired and held indefinitely. A shared lock is acquired when a > select is made, but only held as long as it takes to actually do the reading > for that sel

Re: [web2py] Re: database locking web2py vs. "external" access...

On Sat, Jun 23, 2012 at 1:54 PM, Anthony wrote: >> > You're forgetting another "layer".  DAL leverages on DBAPI >> > implementation of sql drivers. >> >> Well, that's not exactly good news for transparency and ease of >> documentation. > > DB API is just a specification for Python database drivers

Re: [web2py] Re: database locking web2py vs. "external" access...

> > > You're forgetting another "layer". DAL leverages on DBAPI > implementation of > > sql drivers. > > Well, that's not exactly good news for transparency and ease of > documentation. > DB API is just a specification for Python database drivers to follow to ensure some degree of similarit

Re: [web2py] Re: database locking web2py vs. "external" access...

> > > I believe so. The shared lock does not last forever -- only as long as > it > > takes to execute the select. Once the data have been read, the shared > lock > > (for that particular select) is released. > > Yes, if autocommit is on. The part you didn't quote was that > autocommit can b

Re: [web2py] Re: database locking web2py vs. "external" access...

On Wed, Jun 20, 2012 at 9:06 PM, Anthony wrote: >> Sooo Have I misunderstood the SQLite locking description? > > > I believe so. The shared lock does not last forever -- only as long as it > takes to execute the select. Once the data have been read, the shared lock > (for that particular selec

Re: [web2py] Re: database locking web2py vs. "external" access...

On Wed, Jun 20, 2012 at 7:04 PM, Niphlod wrote: > You're forgetting another "layer".  DAL leverages on DBAPI implementation of > sql drivers. Well, that's not exactly good news for transparency and ease of documentation. > A little more complicated: get on an infinite loop inserting 10 rows, > w

Re: [web2py] Re: database locking web2py vs. "external" access...

> > Sooo Have I misunderstood the SQLite locking description? > I believe so. The shared lock does not last forever -- only as long as it takes to execute the select. Once the data have been read, the shared lock (for that particular select) is released. Anthony --

Re: [web2py] Re: database locking web2py vs. "external" access...

You're forgetting another "layer". DAL leverages on DBAPI implementation of sql drivers. Actually, there is no "begin" transaction on python. Usually you instantiate a cursor, do operations, try to commit, if exception is raised you rollback. You can do all kind of tests for your background

Re: [web2py] Re: database locking web2py vs. "external" access...

On Wed, Jun 20, 2012 at 5:53 PM, Anthony wrote: > Can you explain how whether SQLite autocommits or not matters in this case? > The DAL documentation recommends doing a db.commit() in scripts and > background tasks like this precisely because it doesn't want to assume any > autocommitting is happe

Re: [web2py] Re: database locking web2py vs. "external" access...

On Tue, Jun 19, 2012 at 3:31 PM, Niphlod wrote: > Yep, I don't know exactly the defaults, but I happen to see some major > differences against various systems with standard python distribution. Ugh, at least, for the most part, it still all 'just works'. :-/ > Moreover, it's just for this kind

Re: [web2py] Re: database locking web2py vs. "external" access...

> > I'm looking for The Book to tell me what to expect. > If it doesn't tell me, then I don't know if the current behaviour > (once I discover it) will be carried forward to future versions of > Web2py as part of the backwards compatibility promise, or, if the > current behaviour is just a qui

Re: [web2py] Re: database locking web2py vs. "external" access...

On Tue, Jun 19, 2012 at 1:33 PM, Anthony wrote: > I'm not sure it's feasible or desirable for the DAL to try to normalize > every last behavior across all database backends. What exactly would you > have the DAL do differently in this case? Actually what I'm looking for is in the documentation, n

Re: [web2py] Re: database locking web2py vs. "external" access...

> > I was being half kidding, but only half. :-) > given that that phrase could initiate another round of "web2py sucks" and that is actually a simple statement to say that that behaviour occurs only with standard sqlite, I was not kidding at all :P > > Maybe I should make a slice on how t

Re: [web2py] Re: database locking web2py vs. "external" access...

> > > I don't think web2py is doing any magic -- I think it just relies on the > > standard database and database driver behavior. > > "There in lies the rub" - the problem is that The Book isn't really > very easy to use to find out just what the DAL does for each DB and > what "shows through

Re: [web2py] Re: database locking web2py vs. "external" access...

On Mon, Jun 18, 2012 at 4:02 AM, Niphlod wrote: >> Ok, just to double check, that is for all DB back-ends or just for SQLite? >> > This is true for every backend. Thanks! > Web2py is not blocked, and again, it's only with SQLite that there is the > problem. From the first update on the queue to

Re: [web2py] Re: database locking web2py vs. "external" access...

On Mon, Jun 18, 2012 at 2:07 AM, Anthony wrote: > I don't think web2py is doing any magic -- I think it just relies on the > standard database and database driver behavior. "There in lies the rub" - the problem is that The Book isn't really very easy to use to find out just what the DAL does for

Re: [web2py] Re: database locking web2py vs. "external" access...

> > Ok, just to double check, that is for all DB back-ends or just for SQLite? > > This is true for every backend. > > > Ah, OK. So it would be easier to write code that uses any DB except > for SQLite... Hmmm > It's not a question of "easier" or "harder" : as always it's just choosing th

Re: [web2py] Re: database locking web2py vs. "external" access...

> > > Take it like that. Transactions are a way to provide consistent data to > > every reader/writer connected to a database. > > Right, I've got that part down, it is the "what is the magic that > Web2py is doing behind my back" part that I was (and still a little > bit) unclear on. > I do

Re: [web2py] Re: database locking web2py vs. "external" access...

On Sat, Jun 16, 2012 at 5:56 PM, Niphlod wrote: > The bit of magic that I forgot to tell explicitely is that db.commit() > commits every opened transaction and starts a new one :D Ok, just to double check, that is for all DB back-ends or just for SQLite? > Note, if your site does mostly reading

Re: [web2py] Re: database locking web2py vs. "external" access...

No problem at all. The bit of magic that I forgot to tell explicitely is that db.commit() commits every opened transaction and starts a new one :D just to be precise: "locking" is true currently only for sqlite engine, and that's because how sqlite works (it's like that also outside web2py world

Re: [web2py] Re: database locking web2py vs. "external" access...

On Sat, Jun 16, 2012 at 2:38 PM, Niphlod wrote: > if you're familiar with database transactions, web2py istantiate a new > transaction every request and commit (if no exceptions are thrown > automatically) at the end of the request. Yup, that part makes sense. And I assume it is true regardless o

[web2py] Re: database locking web2py vs. "external" access...

if you're familiar with database transactions, web2py istantiate a new transaction every request and commit (if no exceptions are thrown automatically) at the end of the request. in a module, if you want to roll your own "daemon", a transaction is initiated at the start of the script. You have