The backup mechanism should be changed to not require a read-level lock for the duration of the backup. It would be nice to be able to make a backup copy while updates or transactions continue to be made. I don't care if updates made after the backup starts are excluded, that would be fine.

Having writes blocked like this precludes using EDB for some applications.

Arthur


>The backup mechanism should be changed to not require a read-level lock for the duration of the backup. It would be nice to be able to make a backup copy while updates or transactions continue to be made. I don't care if updates made after the backup starts are excluded, that would be fine.
>
>Having writes blocked like this precludes using EDB for some applications.

What would happen if you need to restore and the database was then out of sync? If any form of RI is to be allowed (either at database or program level) this needs to be enforced even when backing up.

As a quick and dirty mechanism have you thought about one full backup at intervals (even if the interval is the first time the app is run) followed by transactional backup using the publishing system to generate the transactions?

Roy Lambert [Team Elevate]

Roy Lambert wrote:

Arthur


What would happen if you need to restore and the database was then out of sync? If any form of RI is to be allowed (either at database or program level) this needs to be enforced even when backing up.

As a quick and dirty mechanism have you thought about one full backup at intervals (even if the interval is the first time the app is run) followed by transactional backup using the publishing system to generate the transactions?

Roy Lambert [Team Elevate]
>>

I don't know what you mean by RI. The backup process should run like a long running transaction. Any changes that happen after the backup starts are unseen by the backup, making it essentially a point-in-time copy. There wouldn't be a sync problem since as far as the backup transaction process is concerned nothing changed while it was running.

I did look at the publishing aspect, but then I read the part that says SAVE UPDATES does the same thing: read-lock on the db while it copies the changes. For a 24x7 real-time application, being locked out like that is just unacceptable.

Arthur,

AFAIK transaction also locks the tables included in it so doing a backup
using transaction would still have the same issue you're seeing now as
whole DB would be locked and other connections would not be able to
edit/insert/delete data.

For our DBISAM based product we actually modified the DBISAM source code
to exclude the DB lock and do individual table locks (not ideal but
worked for us) - RI would generally be an issue but in our case it was
acceptable risk.

To achieve this would probably be a bigger undertaking at engine level
so Tim would need to comment.

Raul


On 12/13/2012 9:14 AM, Arthur Williams wrote:
> I don't know what you mean by RI. The backup process should run like a long running transaction. Any changes that happen after the backup starts are unseen by the backup, making it essentially a point-in-time copy. There wouldn't be a sync problem since as far as the backup transaction process is concerned nothing changed while it was running.

Arthur

>I don't know what you mean by RI. The backup process should run like a long running transaction. Any changes that happen after the backup starts are unseen by the backup, making it essentially a point-in-time copy. There wouldn't be a sync problem since as far as the backup transaction process is concerned nothing changed while it was running.

RI is relational integrity.

Transactions also apply a write lock on all affected tables (ie the entire database or those you've told it to include). They have to. If it didn't you wouldn't be able to roll back. You'll see a number of posts of Elevate's newsgroups all saying keep transactions short because they lock users out.

>I did look at the publishing aspect, but then I read the part that says SAVE UPDATES does the same thing: read-lock on the db while it copies the changes. For a 24x7 real-time application, being locked out like that is just unacceptable.

Depends how long it takes. If you have a OLTP 24x7 system then I think you need to look at a different db system (no disrespect Tim) or a different mechanism such as RAID and shadowing.

Roy Lambert [Team Elevate]

Roy Lambert wrote:

Arthur

Transactions also apply a write lock on all affected tables (ie the entire database or those you've told it to include). They have to. If it didn't you wouldn't be able to roll back. You'll see a number of posts of Elevate's newsgroups all saying keep transactions short because they lock users out.

>I did look at the publishing aspect, but then I read the part that says SAVE UPDATES does the same thing: read-lock on the db while it copies the changes. For a 24x7 real-time application, being locked out like that is just unacceptable.

Depends how long it takes. If you have a OLTP 24x7 system then I think you need to look at a different db system (no disrespect Tim) or a different mechanism such as RAID and shadowing.

Roy Lambert [Team Elevate]
>>

That's why I said like a transaction, at least in the sense that it doesn't see changes from other sessions. I don't know if EDB can run something like a transaction without being an actual transaction.

It may well be impossible to have such a backup process in EDB, I don't know. For this particular project it meant, as you said, that I have to use a different DB system for the part of the project that requires unfettered DB access.

Arthur


>It may well be impossible to have such a backup process in EDB, I don't know. For this particular project it meant, as you said, that I have to use a different DB system for the part of the project that requires unfettered DB access.

My guess would be that such an approach will only be possible when Tim brings out his enterprise version which will only access the database through c/s and probably only then if access is restricted to a single server. Do that and any data entry, deletes etc can be buffered until such time as the backup has been processed.

If I was writing the backup system I'd want it to as fast as possible which would mean totally locking the files and doing a simple byte (or block) copy. This needs little intelligence and is about as fast as you'll get. Compression/encryption will slow it down a bit but may be wanted. With the current approach of you can access the data via fileserver or clientserver and have several server engines all accessing the same data its an impossibility (or at least bloody difficult) to buffer all impending database alterations. Here is one potential nightmare scenario:

1. User (I) adds a new customer (A).
2. Backup starts.
3. Another user (II) realises that customer A already exists and deletes the record. At this point the backup has already backed up a part of A's record
4. User I continues adding customer B
5. ElevateDB (or DBISAM) reuses Customer A's space for Customer B
6. Backup continues and writes a part of customer B's record
7. Backup completes
8. Something catastrophic happens and you restore the backup

You now have a nice amalgam of customer A and customer B

Now this is a) unlikely and b) probably won't happen anyway because you'd write the backup to respect individual record locks wouldn't you

One consequence is its going to be a damn sight slower.

Another one - lets say you have a routine that deletes associated records when a master record is deleted. This starts and the backup reaches the detail records when the routine is half way through deleting them. But to compensate you make sure the backup routine respects individual transaction locks.

I can add a lot more ideas which say that for me and my peace of mind its best to lock the database (or at least the tables I'm interested in).

Now watch Tim post something making me look a complete moron <vbg>

Roy Lambert [Team Elevate]

Windows already has a volume shadow copy service that allows one to
grabd a point in time snapshot of the volume so we could it today to
grab a EDB backup without any locks.

There is a small issue of VSS not knowing the state of EDB at the time
but for most of us it's likely good enough backup.

Ideal solution would be EDB VSS writer app that would make sure EDB DB
is consistent when snapshot starts.

Raul

On 12/13/2012 11:40 AM, Roy Lambert wrote:
>
>> >It may well be impossible to have such a backup process in EDB,

Arthur,

<< The backup mechanism should be changed to not require a read-level lock
for the duration of the backup. It would be nice to be able to make a backup
copy while updates or transactions continue to be made. I don't care if
updates made after the backup starts are excluded, that would be fine. >>

How big of a database are you backing up ?   Most databases back up pretty
quickly, and any transactions will simply wait.

The other alternative, which Roy mentioned, is to use replication to log
updates and then do a SAVE UPDATES as a form of incremental backup.  So,
full backup on weekends or sometime that there is the possibility of a few
minutes of "no writes", and incremental backups the rest of the time.

Tim Young
Elevate Software
www.elevatesoft.com

Arthur,

<< I did look at the publishing aspect, but then I read the part that says
SAVE UPDATES does the same thing: read-lock on the db while it copies the
changes. For a 24x7 real-time application, being locked out like that is
just unacceptable. >>

SAVE UPDATES is only saving the deltas since the last SAVE UPDATES run, so
if you run it every hour, etc. then the SAVE UPDATES will only have a
read-lock on the database for a very, very short period of time.

Tim Young
Elevate Software
www.elevatesoft.com