Hi,

We are using DBISAM 3.30 and I have a utility that uses SQL to backup
databases within a transaction. e.g. (not the real code but close enough)

StartTransaction();
try
  for i := 0 to ListOfTables.Count - 1 do
    ExecuteQuery('select * into ' + BackupTable + ' from ' +
ListOfTables[i] + ';');
  Commit();
except
  on E: Exception do begin
    LogException(E);
    RollBack();
  end;
end;

All well and good, EXCEPT that clients cannot use the system while the
backup is being implemented! After reading your online manual, I can see
that doing a TDBISAMDatabase.StartTransaction() basically puts a lock
over the whole database and restricted transaction puts table locks.

Is this correct? If so, can you please explain why this is? (instead of
record locks on relevant changed data like other SQL Servers). I thought
the whole point of transactions were to get a snapshot of data in time
and only putting record locks if data has changed.

Is there a way I can get a snapshot of current data and take a backup
while still allowing the users to use the system?

Thanks in Advance,

Jaweed,

<< All well and good, EXCEPT that clients cannot use the system while the
backup is being implemented! After reading your online manual, I can see
that doing a TDBISAMDatabase.StartTransaction() basically puts a lock over
the whole database and restricted transaction puts table locks. >>

First of all, don't use SELECT INTO for backup.  That will do a
record-by-record copy, and it is very slow.  Do a normal file system copy of
the table files after starting the transaction.  The backup will be fast
enough that the time won't matter for the other users, especially if it is
done during non-peak usage.

<< Is this correct? If so, can you please explain why this is? (instead of
record locks on relevant changed data like other SQL Servers). I thought the
whole point of transactions were to get a snapshot of data in time and only
putting record locks if data has changed. >>

I'm sorry, but it would take pages and pages to explain to you why record
locks are not sufficient to provide the proper transaction isolation in a
multi-user (non-C/S) environment, which is a mode in which DBISAM operates.
You'll simply have to take my word for it that it is not possible to have
transaction isolation levels other than dirty read in such an environment
without restrictive table locking.

<< Is there a way I can get a snapshot of current data and take a backup
while still allowing the users to use the system? >>

A backup *is* a snapshot of the data, and the only way to get a snapshot in
DBISAM is to "freeze" the data in time.  The transaction does that.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim Young [Elevate Software] wrote:
> Jaweed,
>
> << All well and good, EXCEPT that clients cannot use the system while the
> backup is being implemented! After reading your online manual, I can see
> that doing a TDBISAMDatabase.StartTransaction() basically puts a lock over
> the whole database and restricted transaction puts table locks. >>
>
> First of all, don't use SELECT INTO for backup.  That will do a
> record-by-record copy, and it is very slow.  Do a normal file system copy of
> the table files after starting the transaction.  The backup will be fast
> enough that the time won't matter for the other users, especially if it is
> done during non-peak usage.
>
> << Is this correct? If so, can you please explain why this is? (instead of
> record locks on relevant changed data like other SQL Servers). I thought the
> whole point of transactions were to get a snapshot of data in time and only
> putting record locks if data has changed. >>
>
> I'm sorry, but it would take pages and pages to explain to you why record
> locks are not sufficient to provide the proper transaction isolation in a
> multi-user (non-C/S) environment, which is a mode in which DBISAM operates.
> You'll simply have to take my word for it that it is not possible to have
> transaction isolation levels other than dirty read in such an environment
> without restrictive table locking.
>
> << Is there a way I can get a snapshot of current data and take a backup
> while still allowing the users to use the system? >>
>
> A backup *is* a snapshot of the data, and the only way to get a snapshot in
> DBISAM is to "freeze" the data in time.  The transaction does that.
>

Hi Tim,

Even if I do a file system backup after starting the transaction, User's
still won't be able to update data (because a transaction has been
started?)? Is this correct?

One of our client's database is about 1.3 gb so even a file system
backup will take some time.

Thanks

Jaweed,

<< Even if I do a file system backup after starting the transaction, User's
still won't be able to update data (because a transaction has been
started?)? Is this correct? >>

Correct.

<< One of our client's database is about 1.3 gb so even a file system backup
will take some time. >>

Our database is a little over 4 gigs, and a full version 4.x backup takes a
little under 7 minutes, including compression.  I would estimate that a file
system copy would be around the same time or slightly longer, depending upon
the speed of the hard drives, etc.

--
Tim Young
Elevate Software
www.elevatesoft.com