On 02/11/2011 17:33, Tom Evans wrote:
On Wed, Nov 2, 2011 at 4:22 PM, Marco Paolini<markopaol...@gmail.com> wrote:
On 02/11/2011 17:12, Tom Evans wrote:
If you do a database query that quickly returns a lot of rows from the
database, and each row returned from the database requires long
processing in django, and you use mysql_use_result, then other mysql
threads are unable to update any table being used, where as if you do
the same thing with mysql_store_result, the tables are unlocked as
soon as the client has retrieved all the data from the server.
other connections in other transactions are locked too?
Yes. The exact wording from the C API:
"""
On the other hand, you shouldn't use mysql_use_result() if you are
doing a lot of processing for each row on the client side, or if the
output is sent to a screen on which the user may type a ^S (stop
scroll). This ties up the server and prevent other threads from
updating any tables from which the data is being fetched.
"""
mysql treats the table as in use until the result is freed.
If this behaviour was in place, then you wouldn't have even raised the
original query - the approach you were using was to iterate through a
result set and modify the table you are fetching from. With
mysql_use_result, you would have deadlocked that table in the mysql
server as soon as you tried to update it without first completing the
first query.
Yeah, the discussion has drifted a bit from it's staring point
so, summarizing again:
- 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)
Marco
--
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.
