We're currently running the VCL Trial version and we like it so far, but also have some concern on concurrency and how locks should be carefully managed.

I remember reading somewhere about an upcoming ver. 3.0 that will get rid of the multi file architecture in a favor of a single database file, with the possibility of a more improved concurrency. Is that true? and if so how far it is?

Thanks everybody and have a Merry Christmas...

Regards,
Mario

Mario,

<< I remember reading somewhere about an upcoming ver. 3.0 that will get rid
of the multi file architecture in a favor of a single database file, with
the possibility of a more improved concurrency. Is that true? and if so how
far it is? >>

I'm working on trying to get it out in 2013.  Work stopped on it for a while
during Elevate Web Builder development, but will be starting back up
shortly.

If you have any other questions, please let me know.

Tim Young
Elevate Software
www.elevatesoft.com

Mario,

<< We're currently running the VCL Trial version and we like it so far, but
also have some concern on concurrency and how locks should be carefully
managed. >>

BTW, what specifically are you concerned about with regard to
locking/concurrency ?   It is often the case that certain features simply
don't play out the way that you would expect in actual usage, so they seem a
lot worse than they really are.

Tim Young
Elevate Software
www.elevatesoft.com

Tim,

Our mains concerns are the following...

1.We will have a high activity table (Lots of inserts/updates) with several potential users (10~50 max) and writes to this table will trigger updates to few other tables. I understand that for this to happen safely, a lock should be issue for the whole/all tables affected by the transaction, potentially given us slow performance.

2.The other concern is regarding the backup process. It is also my understanding that online backup are not possible without locking the entire database (to safeguard its integrity) and as the database grows larger the amount of time spent doing backup (5~10 minutes possible, just a guess..) might bother some users that might be lockout of the database.

Some further information to the database and application that might help in your response...

1.Application will be developed with Delphi and DataSnap, caching as much as possible on the client side (mainly Lookups and such) and trying to make transactions as brief as possible.

2.Database would be 90~100 tables, most relations are highly normalized, still finishing design.

3.GUIDs would be used for all primary keys (One single surrogate key for each table)

4.Duplicates are watched through unique index on each table that requires it.

I know is hard/dangerous to give a date for the release of ver.3.0, but I wonder if you could share an estimate timeframe, so we might plan accordingly.

Thanks for your time and help, and congratulation on such a great product!

Regards,
Mario

Mario

Some responses

>>Our mains concerns are the following...

>>1.We will have a high activity table (Lots of inserts/updates) with several potential users (10~50 max) and writes to >>this table will trigger updates to few other tables. I understand that for this to happen safely, a lock should be issue >>for the whole/all tables affected by the transaction, potentially given us slow performance.

I have 1 system with a similar level of use to this, plus about a dozen smaller systems. I have some tables with 20 users adding 1,000s of rows each, hourly. Running on a single Win2008 Server EDBSRVR machine the client is happy with how fast it runs & most hold-ups are on the network not on EDB back end.

>>2.The other concern is regarding the backup process. It is also my understanding that online backup are not >>possible without locking the entire database (to safeguard its integrity) and as the database grows larger the >>amount of time spent doing backup (5~10 minutes possible, just a guess..) might bother some users that might >>be lockout of the database.

I have jobs which backup the database. I do not believe that these slow down or lock the DB. This is with DBs of up to 1 Gig (no blob data, so quite a lot of rows).

Another way to play this is to use EDB's Replication Architecture.

1. Have your main server.
2. PUBLISH it.
3. Save Updates on this db regularly (every few min if you like). This is a quick and fairly light-weight process, I think, and results in a snapshot update file.
4. Copy the update file to a new location (ideally remote from your main server).
5. Apply the update to your "backed-up" database. Then you have a backup which is actually a fall-over capable copy of your DB.

... if users don't need to see each other's posts/inserts live I actually think it is best to work with multiple servers anyway, and regularly publish/replicate between them. This reduces load on an individual server and enables a fall-over fail-safe to be built into your architecture.


>>Some further information to the database and application that might help in your response...

>>1.Application will be developed with Delphi and DataSnap, caching as much as possible on the client side (mainly >>Lookups and such) and trying to make transactions as brief as possible.

If you use Delphi with EDB in Client/Server mode, serving data to the application via an IP address I am really not sure you need to go to the trouble of Datasnap, though you can.

I download lookup-lists (of which I have a large number) and hold them in StringLists in Delphi, but apart from that I do everything direct to the server through simple TEDBQuery descendent components.

---------

I can't really comment on your other points ... they all seem like good design & will work well with EDB. Certainly EDB has no problem with normalization & I use it as much as I can.

On GUIDs, I used EDB GUIDs for 1 app, but now I have returned to using INTEGER primary keys ... I simply have an EDB function which hands out unique INTs and increments. With multiple Servers you can hand each server a ranged INT (i.e. between 1 and 1,000,000, 1,000,001 and 2,000,000) and then have a master table in 1 location holding the next range.

GUIDs are good, but I think they over-complicate matters, lack orderliness, and I have read that in some cases you can end up with duplicates!

Adam,

Thank you very much on your response, especially on the replication tip for faster backups!

We're already reconsidering the benefits of using DataSnap for this application and might choose to leave it out as much of the required functionality could be achieve it with stored procedures/triggers without the added deployment complexity of DataSnap.

Regarding the application you mentioned, the one with 20 concurrent users pumping 1000 recs hourly, could you tell me more about it? Such as total size of the database, conditions of operation (24x7?) and for how long do you have it running now.

Greatly appreciate you feedback on this, if it doesn't bother you, of course.

Regards,
Mario

Mario,

<< 1.We will have a high activity table (Lots of inserts/updates) with
several potential users (10~50 max) and writes to this table will trigger
updates to few other tables. I understand that for this to happen safely, a
lock should be issue for the whole/all tables affected by the transaction,
potentially given us slow performance. >>

As long as the transactions are kept as short as possible, then you will be
fine.  Unless you have some calculations that really take a long time, then
you're not keeping the affected tables locked for very long at all, and any
other sessions/users can still read from the tables during the transaction.
The most restrictive locking aspect of a transaction occurs during the
commit phase when a write lock is placed on the affected tables.  Such a
lock will only be in place as long as it takes to perform the actual writes
to disk.

Also, be sure an try to use restricted transactions that only involve the
affected tables in the transaction.  There's no need to involve the whole
database in the transaction if you're only updating 3-4 tables.

<< 2.The other concern is regarding the backup process. It is also my
understanding that online backup are not possible without locking the entire
database (to safeguard its integrity) and as the database grows larger the
amount of time spent doing backup (5~10 minutes possible, just a guess..)
might bother some users that might be lockout of the database. >>

Do you plan on doing the backup during operating hours ?  Or are operating
hours 24-7 ?

<< I know is hard/dangerous to give a date for the release of ver.3.0, but I
wonder if you could share an estimate timeframe, so we might plan
accordingly. >>

I can't give anything definite, but I can say what I'm working on in 2013
going forward with EDB:

1) EDB 3.x
2) Mac support (just got our new Mac build machine in this week).
3) Lots of EDB Manager productivity improvements

If you have any other questions, please let me know.

Tim Young
Elevate Software
www.elevatesoft.com

Tim,

Thank you for the heads up on restricted transactions I'll read more on this subject.

On the 24 x 7 front, is quite possible as some customers might use the application this way, but most users might choose to backup every  1 hour (incremental?), just to be safe.

Cool list of features for 2013, hopefully we'll EDB ver 3 with single file support ealier in the year (Q2 maybe? )

regards,
Mario

"Tim Young [Elevate Software]" wrote:

Mario,

<< 1.We will have a high activity table (Lots of inserts/updates) with
several potential users (10~50 max) and writes to this table will trigger
updates to few other tables. I understand that for this to happen safely, a
lock should be issue for the whole/all tables affected by the transaction,
potentially given us slow performance. >>

As long as the transactions are kept as short as possible, then you will be
fine.  Unless you have some calculations that really take a long time, then
you're not keeping the affected tables locked for very long at all, and any
other sessions/users can still read from the tables during the transaction.
The most restrictive locking aspect of a transaction occurs during the
commit phase when a write lock is placed on the affected tables.  Such a
lock will only be in place as long as it takes to perform the actual writes
to disk.

Also, be sure an try to use restricted transactions that only involve the
affected tables in the transaction.  There's no need to involve the whole
database in the transaction if you're only updating 3-4 tables.

<< 2.The other concern is regarding the backup process. It is also my
understanding that online backup are not possible without locking the entire
database (to safeguard its integrity) and as the database grows larger the
amount of time spent doing backup (5~10 minutes possible, just a guess..)
might bother some users that might be lockout of the database. >>

Do you plan on doing the backup during operating hours ?  Or are operating
hours 24-7 ?

<< I know is hard/dangerous to give a date for the release of ver.3.0, but I
wonder if you could share an estimate timeframe, so we might plan
accordingly. >>

I can't give anything definite, but I can say what I'm working on in 2013
going forward with EDB:

1) EDB 3.x
2) Mac support (just got our new Mac build machine in this week).
3) Lots of EDB Manager productivity improvements

If you have any other questions, please let me know.

Tim Young
Elevate Software
www.elevatesoft.com

Mario,

<< Cool list of features for 2013, hopefully we'll EDB ver 3 with single
file support ealier in the year (Q2 maybe? ) >>

I've started back working on the single-file disk management, so hopefully
we'll see something by early summer.

Tim Young
Elevate Software
www.elevatesoft.com