On Nov 3, 11:09 pm, Marco Paolini <markopaol...@gmail.com> wrote:
> > Now, calling the .iterator() directly is not safe on SQLite3. If you
> > do updates to objects not seen by the iterator yet, you will see those
> > changes. On MySQL, all the results are fetched into Python in one go,
> > and the only saving is from not populating the _results_cache. I guess
> > Oracle will just work with the .iterator() approach. Without
> > the .iterator() call, it will consume the memory, as ._results_cache
> > will be populated.
>
> it *is* safe to call .iterator with sqlite because our ORM fetches all
> rows into memory (see below and django/django/db/models/sql/compiler.py)
Doh, a little miss in my reading of the code, in the end of
compiler.py execute_sql is this little snippet:
"""
if not self.connection.features.can_use_chunked_reads:
# If we are using non-chunked reads, we return the same
data
# structure as normally, but ensure it is all read into
memory
# before going any further.
return list(result)
"""
That might explain something... :)
The test data, which shows a big memory saving when using fetchmany()
as opposed to fetchall in the compiler.py for PostgreSQL is probably
because even if the data is fetched in one go from the server, it is
transformed to Python objects in bulks by fetchmany(). I am not sure
of this. This confused me, sorry for that.
So, what you said before is correct:
- mysql supports chunked fetch but will lock the table while
fetching is in progress (likely causing deadlocks)
- postgresql does not seem to suffer this issue and chunked fetch
seems doable (not trivial) using named cursor
- oracle does chunked fetch already (someone confirm this, please)
- sqlite3 COULD do chunked fetch by using one connection per cursor
(otherwise cursors will not be isolated)
The SQLite3 shared cache mode seems to suffer from the same problem
than mysql:
"""
At any one time, a single table may have any number of active read-
locks or a single active write lock. To read data a table, a
connection must first obtain a read-lock. To write to a table, a
connection must obtain a write-lock on that table. If a required table
lock cannot be obtained, the query fails and SQLITE_LOCKED is returned
to the caller.
"""
So, if you have an open connection to a table in one cursor (which is
a separate connection), and you try to modify the table while that
connection is open by another cursor, it seems you will deadlock.
You could use the read uncommitted isolation level, but then you will
have the same problem you have now when using fetchmany() - updates
might be seen in the other cursor's objects. The SQLite transaction
isolation implementation is not MVCC. It is likely it simply does not
support multiple row versions, and this means you can't update a table
and expect to still get the old version from the server in another
cursor.
- Anssi
--
You received this message because you are subscribed to the Google Groups
"Django developers" group.
To post to this group, send email to django-developers@googlegroups.com.
To unsubscribe from this group, send email to
django-developers+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/django-developers?hl=en.
