Last blog I have talked about the problem of database lockup but how can we solve it?
Shared Tables: Shared Tables could be a easy to go and a dirty solution that is very common. But be aware that it is high maintenance.
Using mysql we can use FEDERATED ENGINE (http://dev.mysql.com/doc/refman/5.1/en/federated-storage-engine.html) to do this. We have to create a federated table based on the table at another remote location that we want.
CREATE TABLE remote_user ( username varchar(20) NOT NULL, password varbinary(20) NOT NULL, PRIMARY KEY(username) ) ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='mysql://username:password@someip:port/db/user’;
Database View : A database view is a comparatively better approach when the cases are simple because it allows another representation of database model which is more suitable. Most amazing thing about database view is that it supports wide range of databases. But for heavy use cases we can see performance issues. While considering database view we must ensure that both of the databases can connect with each other without any network or firewall issue. Most of the database views are read only, updating them according to need might get tricky.
CREATE TABLE federated_table (
[column definitions go here]
)
ENGINE=FEDERATED
CONNECTION='mysql://username:password@someip:port/db/user’;
Triggers:
Database triggers might come handy where one database operation will trigger another database update. We can bind to AFTER INSERT, AFTER UPDATE, and AFTER DELETE triggers.
CREATE TRIGGER user_bi BEFORE INSERT ON user FOR EACH ROW BEGIN INSERT INTO remote_user (username,password) VALUES (NEW.username,NEW.password); END
Data Virtualization: When we are dealing with micro services possibly some of our databases are running using Mysql while other services are running other DBMS. In that case Data Virtualization strategy is necessary. One open source data virtualization platform is Teiid. But when dealing with data virtualization strategy we must know that if we are dealing with stale data or not, as it will have serious performance issue as it will add another hop as the data is not being accessed directly from database.
Event sourcing: Rather then making database operatins we can consider designing it as a stream of events that goes one after another through as message broker. So it does not matter how many users are accessing your database it will never lock up your database but it would take more time to process the data.
Change Data Capture: Another approach is to use Change Data Capture (CDC), is an integration strategy that captures the changes that are being made to a data and makes them available as a sequence of events in other databases that needs to know about these changes. It can be implemented using Apache Kafka, Debezium and so on.