Introducing The Service Broker
In an effort to improve the saving efficiency of the MEX database, and also to alleviate data loss, we have introduced The Service Broker functionality to the MEX SQL Database.
In the last year we found that more and more deadlocks were occurring on MEX databases causing serious data loss. In this scenario when you enter data and another process is also running at the same time and trying to save that data. They both try to execute, but instead only one is run locking out the other process.
Read more about deadlocks here: What are Deadlocks in SQL
To counter this, a new approach has been employed in the form of The Service Broker (TSB), essentially set up a queue. Transactional steps are followed and all data gets saved successfully in the database. Replacing the coliseum that was, and introducing a queue that guarantees that all your data is successfully saved in MEX’s SQL database.
How do we do this?
In the MEX database we have set up TSB to work off our database triggers. Through our rigorous work in identifying deadlocks, high traffic tables and mobile specific tables were identified and programmed TSB to create a queue on update/insert/delete of these tables. This allows the tasks to be executed asynchronously, in the process decouples that task from the actual table update/insert/delete.
If you have any questions please contact our support team at support@mex.com.au or call +61 7 3392 4777.