Multiple Database Users And Updating

Databases
1

I’m looking for some advice/best practice regarding a multi user database setup.

Picture if you will, a small server running (probably) Mysql server, with a modest size database (10-15 tables, a few hundred rows in each).

Connecting to that database will be a number of client machines. The maximum would be 10, but typical usage would see 6 or 7 connected at any given time.

Each client will run the same software, which will enable them to view and change the database contents, typically using a series of listboxes, textfields and Save/Cancel buttons. Nothing out of the ordinary so far then.

Now, when any given user saves changes to the database on the server, I need to find the most efficient way to push those changes out to each of the other clients as quickly as possible. The whole idea depends on how fast these changes can be pushed out, so that as soon as a change is saved to the database, the clients are instantly updated to reflect the changes. This needs to be as fast as possible.

My first thought for this is to setup a thread and timer on each client which simply refreshes the listboxes, and set this to occur every second or two.

But I’m also thinking this would slow things down due to the constant refreshing and network activity, as well as potentially slowing down the performance of the clients.

Is there a way to maybe set something running on the server that would push the changes to the clients only when a change is made ? Or a more efficient method of updating the clients, essentially in real-time ?

The clients would all be conected over standard ethernet.

I’m open to the possibility of using a more fully-featured database, and I’m assuming that any possible solution would be able to scale upwards to enable this for more tables as required. The number of users is never going to exceed 10 at any given time.

2

use postgresql & its notifications which can tell you what table & row was affected (see CheckForNotifications & ReceivedNotification on http://documentation.xojo.com/index.php/Postgresql)

Mysql doesn’t have such a mechanism

And you avoid ANY ambiguity about whether you need to buy a license or not - you don’t with postgresql. Period.

3

I have 15 user with no problems using postgresql

4

Interesting, thanks for the replies. I havent looked at postgresql in detail yet, time to do some reading.

I had visions of the network getting bogged down or having to code some kind of ‘helper’ app that notified the clients of an update. Sounds like the postgresql notifications are exactly what Im looking for.

:slight_smile:

5

DB’s that support notifications like this make doing “push updates” simpler since you don’t have to query the DB all the time to find out IF things changed

very handy

6

I see we have to use the timer event here. Would once per second be reasonable or too much ?

7

I too was a little surprised by having to poll - not sure why that is and haven’t investigated.

Once per second is probably lots unless you expect a really busy system.

8

Thanks, I will look into this. I do expect the system to be very busy at times.

9

Looks like this is just how PG has implemented it in the API used in the plugin (see http://www.postgresql.org/docs/9.3/static/sql-listen.html)
The client has to check for notifications periodically.
But I’d expect you could make the client check more frequently without much penalty

10

Since everyone else went in the direction of PostgreSQL, I thought I’d offer an alternative. About 10 years ago (before working for Xojo), I built a system that had similar requirements. We were locked into Microsoft SQL and no built-in notification system.

Basically, if another user happens to have a record open that is or is connected to the changed customer, the other computers on the network are notified that the record changed and their screens are updated. The way we handled this was to use a UDPSocket to “broadcast” the need for the other clients to update and then they could schedule that update at their convenience. This software is still being used today.

UDP is not a guaranteed delivery service, but it was close enough for this project. Keep in mind that we also restricted the editing of the main customer record to one user at a time to prevent overwriting user changes.

11

I just wanted to post a wee update on this thread following the advice I received.

I’ve registered and installed the 3 user version of CubeSQL here for development and testing purposes. I’ve also had a fresh look at the UDP suggestion posted by Greg, and I think thats the road I will go down.

Considering the network example that comes with Xojo, and its code being just a few lines to implement using a UDP socket, I think it should be quite easy doing this.

Also, since each client IP address will be available, a simple ‘keep alive’ message can be routinely sent when there is no other activity going on, maybe every 30 seconds or so.

The advantage using this method is that the clients dont need to keep on polling needlessly.

A simple module that contains a few methods to setup and establish the UDP socket, and wait for incoming messages seems like much less overhead. By standardising the format of the messages exchanged, I can include the database name, table name, row number, insert/edit/delete operation and ask for replies too. I could probably even insert the actual sql of the operation itself, although Im not planning to do that initially.

So while delivery is not guaranteed, polling with a simple keep alive message will achieve the next best thing. Then a simple call to broadcast the information above after a database operation has completed successfully, I think, fulfills this need rather well overall.

Receiving an update or message only when a database operation has been carried out is key here, instead of having to keep on checking for non-existent updates. This will make all the difference.

:slight_smile:

12

Maybe put a link here, or maybe even a project on Github?

13

Just for the record, I am using this technique (PG on the server, clients check for notifications) heavily. Checking for notifications is super fast (I think I once measured 17 ms!), so that you can do it once or twice every second. This works so well that I am using this mechanism to update the UI even on the client that triggered the notification in the first place. The PG plugin does a great job at dealing with this.

14

When a user is in the midst of making data changes in his UI, and a notification is received that the original record has been changed, then one still has to decide whether to discard his changes and reload the updated record or try to sync etc.

You can do the same thing by implementing a timestamp check in an update trigger and implement your rules from there. Triggers are available in all multiuser dbms while notifications are not.

15

Why not add a simple, small table that only house a few records that store your “update state”? When somebody writes something to the database that needs to get pushed to other users, your software also updates the corresponding record in this small table. You then just have to regularly poll this small table to see if there was an update that needs pushing and only when this is the case you’ll need to request the changed data. This way you’re not reading lots of data all the time to see if something’s changed.

I use this system on a 10 user MySQL based system and it works perfectly.

16

[quote=150743:@Mario van Ginneken]Why not add a simple, small table that only house a few records that store your “update state”? When somebody writes something to the database that needs to get pushed to other users, your software also updates the corresponding record in this small table. You then just have to regularly poll this small table to see if there was an update that needs pushing and only when this is the case you’ll need to request the changed data. This way you’re not reading lots of data all the time to see if something’s changed.

I use this system on a 10 user MySQL based system and it works perfectly.[/quote]

You don’t have to read a lot of data, just a timestamp field per record. If you select and alter that record and try to update it back to the database, then the trigger checks if a newer timestamp is existing.

If yes, then do not update.
If no, update and set timestamp to current datetime.

If one does not wish to use triggers, then one still can wrap the whole checking and updating into a transaction:
First select the timestamp and compare to the one you have in the record you wish to update.
Second: if it is still the same timestamp, then update, else refuse update and inform the user.

In Postgresql you can get this information even quicker by implementing notification as suggested above.

17

Yep, I do have a timestamp field for every table in the system, even for what may be insignificant data. This is included as part of the auditing process for the system. Ive even thought about adding a secondary table which logs say the last 100 to 250 transactions, the date/time of the update and the before & after values.

I think some of the higher end dbms systems can keep something similar as a transaction log, but its easy enough to implement even in CueSQL/SQLite/PG.