db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Suresh Thalamati <suresh.thalam...@gmail.com>
Subject Re: logged/unlogged operations
Date Sat, 17 Feb 2007 02:56:09 GMT
Jim Newsham wrote:
> Hello,
> I’d like to back up an online derby database, and would also like to 
> understand how this interacts with other concurrent transactions 
> (regarding visibility, blocking, etc.).  I found some documentation in 
> derbyadmin.pdf.  It mentions logged and unlogged operations, but doesn’t 
> describe what they are, or which database operations are logged and 
> which are unlogged.  Is there another reference that provides more details?

Hi Jim,

I am assuming you are referring to 10.2 version of the Derby. In 10.2 
Backup does not block any user operations (DML/DDL), it just makes any 
un-logged operation to a logged one if an  unlogged operation starts 
after the backup starts copying data or waits if an unlogged operation 
is in progress, if you use SYSCS_UTIL.SYSCS_BACKUP_DATABASE(). If you 
don't the backup to wait for unlogged operation to complete, then you 
can use SYSCS_UTIL.SYSCS_BACKUP_DATABASE_NOWAIT. These two methods are 
described in the admin guide: 

The reason to do automatic conversion of  unlogged operation to logged 
one or make backup wait is,   Derby backup relies on transaction log 
to bring the database to consistent state using the data file copies.

 From the user perspective some operations that are unlogged may run 
slow,because during the backup they are getting logged; but they
will not get blocked for the backup to complete.

What are the unlogged operations in Derby ?

Derby does not have support like unlogged tables , but internally for 
some operations it does not log because it can keep the database 
consistent without logging the data.

I don't remember all the cases, but two main unlogged operations 
cases, I know of are :

1) Index creation:

Only "create index is logged" , but not all the data inserts into the 
index. The reason inserts into the index on create is not logged is , 
if there is a failure , it will just drop the index.

If you create an index when the backup is in progress, it will be 
slower because it has to be logged.

Foreign Keys , Primary Keys create backing indexes. Adding those keys
to an existing table with data  will also run slower.

2) Import to an empty table or replacing all the data in a table.

In this case also data inserts into table are not logged.  Internally
derby creates a new table for the import and changes the catalogs to
point to the new table and drops the original table when import

If you perform such an import operation when backup is in progress ,
it will be slower because data is logged.

I thought some of these cases were mentioned in the docs, looks like
they are not. Please file a Jira entry for the doc improvement.

Hope that helps

View raw message