Re: how to handle big tables, that does not fit into heap memory at once?
Posted by: Mark Matthews
Date: June 03, 2005 02:31PM

mkaktus wrote:
> Hi
> how to handle big tables, that does not fit into
> heap memory at once?
>
> Documentation of ResultSet says that forward-only,
> read-only result set, fetched row by row, are only
> allowed, but with totaly locking involved tables.
> (MySQL Connector/J Documentation - 1.3.2 JDBC API
> Implementation Notes, Resultset )
>
> How to scan/iterate big tabels row by row and make
> updates?
>
> ( Sun's CachedRowSetImpl didnt work either )



Mkaktus,

I'd ask myself first if there's any good reason I'm pulling all those results over to a client to do UPDATEs, when there are much faster ways to do such things with SQL Queries (it is designed to process sets in an optimial way).

If you can't do it in SQL, then it becomes an issue of what table type you're using, and whether you're using different connections to do the updating or not. If you're using InnoDB type tables and the same connection, you _should_ be okay, as it's _your_ connection that holds row-level locks (if any).

The documentation is a bit misleading about the locks that are held, we're updating it to read more like this:

"The earliest the locks these statements hold can be released (whether they be MyISAM table-level locks or row-level locks in some other storage engine such as InnoDB) is when the statement completes.

If the statement is within scope of a transaction, then locks are released when the transaction completes (which implies that the statement needs to complete first). As with most other databases, statements are not complete until all the results pending on the statement are read or the active result set for the statement is closed.

Therefore, if using "streaming" results, you should process them as quickly as possible if you want to maintain concurrent access to the tables referenced by the statement producing the result set.
"

-Mark

Mark Matthews
Consulting Member Technical Staff - MySQL Enterprise Tools
Oracle
http://www.mysql.com/products/enterprise/monitor.html

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.