Real-Time Data – Achieving Real-Time or Near Real-Time with MySQL or MSSQL

javaMySQLsql servertrigger

I work at a startup that is working with different databases among which are MySQL (v4/5) and MSSQL (2014 and older). Different clients we cater to run different dbs. Currently we have a Java program that connects to the db and then pushes data to the API – we tap into the db. We were looking to upgrade the system to have real time updates to the API and then got into a bit of standstill with designing real time updates with a MySQL or MSSQL databases.

One of the developers is pushing for having triggers in either db that run an external command (our java program perhaps) that will initiate a push to the API. Somewhat similar to some NoSQL dbs like Rethink. Another dev is against triggers and is proposing more frequent polling of the db, which will not provide real time, but will get pretty close to real time.

I searched for a while for SQL real time solution, but was not able to find any solid reads with use cases and examples that support either approach. So my questions are:

  • Is frequent polling of the db engine like that of MySQL or MSSQL an ok thing to do? I am talking about 10 or 30 second polling. The db normally gets traffic from 3-4 clients at most and has roughly 20-40k records in table we are interested in. So we'd be adding another client that's polling aggressively.

  • Has anyone ran into actual negative use cases with triggers calling external commands? We won't be using them to guarantee integrity and consistency is also not that big of a concern in our use case (it is, but there is some wiggle room).

I am not a DBA or an experienced dev yet. Quite fresh from CS university with some experience. And all of us are like that so I am concerned that this lack of knowledge might backfire later with clients.

Best Answer

My opinion is that polling in your case (10 seconds is OK to me) is better than the trigger solution. The trigger is closely coupled with your transaction, and can cause lots of un-expected issues that are hard to trouble-shoot. Personally, I have never used trigger to call external application (what if the application fails, long to start, stuck? )

A better (but more difficult solution) can be using service broker to engage with external application.

Related Question