N-way multimaster replication system and method thereof

1 . A processor implemented method, comprising:

routing, by a transaction router, at least one incoming transaction associated with an application to at least one corresponding database server (DBS) by implementing a logic that integrates a geographical proximity mean value (GPMV) and a sender internet protocol (IP) affinity value (SIPAV), wherein the at least one incoming transaction associated with the application corresponds to a sequence of operations performed on at least one corresponding DBS, wherein at least one incoming transaction associated with the application is routed to a failover database server determined by a predefined logic, if at least one corresponding DBS is unavailable to accept traffic, and wherein the sequence of operations corresponds to at least one of (i) a read operation, and (ii) a write operation;

processing, via one or more hardware processors, the at least one incoming transaction to extract at least one data manipulation language (DML) operation written directly by the application using a change data capture mechanism from a database and replicated to each DBS other than a DBS on which the application is directly applying a data through one or more incoming transactions;

generating, via the one or more hardware processors, a primary key by allocating at least one unique identifier if the at least one data manipulation language (DML) operation is inserted on an identified database server (DBS), wherein each primary key corresponds to a numeric value;

updating, via the one or more hardware processors, at least one primary key of a record from the at least one incoming transaction, wherein the record correspond to one or more attributes, and wherein the update corresponds to a change in the one or more attributes; and

dynamically monitoring, via the one or more hardware processors, if there is a conflict associated with a parent and a child association of at least one data manipulation language (DML) operation based on an integrity Foreign key Constraint error obtained by processing at least one inserted primary key on the record from the at least one incoming transaction;

wherein the GPMV corresponds to a score assigned to each potential route based on a geographical proximity, wherein a database server A receives a higher GPMV, if the database server A is closer to a destination than a database server B wherein the SIPAV corresponds to a sender internet protocol (IP) which comprises an affinity value associated with each DBS, and wherein a traffic of each client is routed to closest available DBS based on proximity of the sender IP of each client to the identified closest available DBS.

2 . The processor implemented method of claim 1 , wherein the primary key is generated by:

a) assigning, via the one or more hardware processors, a site identifier (Site_ID) to the identified DBS, wherein the identified DBS comprises at least one sequence incrementor object;

b) obtaining, via the one or more hardware processors, a next value (NextVal) from a sequence (V) of the identified database (DB) incrementing by n, wherein a next incremented value is to be returned as 1+n and 1+2n, and wherein ‘n’ corresponds to number of sites; and

c) generating, via the one or more hardware processors, the primary key (p) by a call sequence object for a next value which returns the sequence (V) of the identified database (DB).

3 . The processor implemented method of claim 1 , wherein at least one incoming transaction is updated based on the primary key by:

a) extracting, via the one or more hardware processors, a candidate primary key identified for update associated with at least one incoming transaction as Vx;

b) determining, via the one or more hardware processors, an originating database server (a route_site_ID) by the candidate primary key and a modulo function n; and

c) routing, via the one or more hardware processors, the update associated with at least one incoming transaction to the originated database server from the client.

4 . The processor implemented method of claim 1 , wherein at least one incoming transaction is updated based on the non-primary key by:

a) creating, via the one or more hardware processors, a framework to accept non-primary key update Structured Query Language;

b) executing, via the one or more hardware processors, another select query on a local database server DBSn, to select one or more potential primary keys to be updated;

c) executing, via the one or more hardware processors, a modulo function on all selected one or more potential primary keys to obtain resultant Route_Site_IDs; and

d) routing, via the one or more hardware processors, a transaction to respective Route_Site_ID and executing an update statement on respective DBS.

5 . The processor implemented method of claim 1 , wherein the conflict associated with the parent and the child association of at least one data manipulation language (DML) operation is dynamically monitored by:

a) checking, via the one or more hardware processors, if there is the integrity Foreign key Constraint error on at least one DML operation is inserted on the identified database server (DBS);

b) determining, via the one or more hardware processors, the primary key of a parent record (Ppk) and calculating an associated Re-Route Site ID;

c) rerouting, via the one or more hardware processors, a transaction to a database server (DBSr) and calculating new PK for a child table; and

d) executing, via the one or more hardware processors, an insert on the DBSr with new PK.

6 . The processor implemented method of claim 1 , wherein an addition of at least one database server by:

a) obtaining, via the one or more hardware processors, a downtime to record a global system change number with a timestamp (T_Add) which are relayed to at least one database server (DBS);

b) assigning, via the one or more hardware processors, a Site_Id n+1 to a new database server DBSn+1;

c) incrementing, via the one or more hardware processors, by value to generate a primary key which changes to n+1; and

d) monitoring, via the one or more hardware processors, the timestamp(T_U) of record to be updated pre update present in the database server.

7 . A system, comprising:

a memory storing a plurality of instructions;

one or more communication interfaces; and

one or more hardware processors coupled to the memory via the one or more communication interfaces, wherein the one or more hardware processors are configured by the instructions to:

route at least one incoming transaction associated with an application to at least one corresponding database server (DBS) by implementing a logic that integrates a geographical proximity mean value (GPMV) and a sender internet protocol (IP) affinity value (SIPAV), wherein the at least one incoming transaction associated with the application corresponds to a sequence of operations performed on at least one corresponding DBS, wherein at least one incoming transaction associated with the application is routed to a failover database server determined by a predefined logic, if at least one corresponding DBS is unavailable to accept traffic, and wherein the sequence of operations corresponds to at least one of (i) a read operation, and (ii) a write operation;

process the at least one incoming transaction to extract at least one data manipulation language (DML) operation written directly by the application using a change data capture mechanism from a database and replicated to each DBS other than a DBS on which the application is directly applying a data through one or more incoming transactions;

generate a primary key by allocating at least one unique identifier if the at least one data manipulation language (DML) operation is inserted on an identified database server (DBS), wherein each primary key corresponds to a numeric value;

update at least one primary key of a record from the at least one incoming transaction, wherein the record correspond to one or more attributes, and wherein the update corresponds to a change in the one or more attributes; and

dynamically monitor if there is a conflict associated with a parent and a child association of at least one data manipulation language (DML) operation based on an integrity Foreign key Constraint error obtained by processing at least one inserted primary key on the record from the at least one incoming transaction;

wherein the GPMV corresponds to a score assigned to each potential route based on a geographical proximity, wherein a database server A receives a higher GPMV, if the database server A is closer to a destination than a database server B, wherein the SIPAV corresponds to a sender internet protocol (IP) which comprises an affinity value associated with each DBS, and wherein a traffic of each client is routed to closest available DBS based on proximity of the sender IP of each client to the identified closest available DBS.

8 . The system of claim 7 , wherein the primary key is generated by:

a) assign a site identifier (Site_ID) to the identified DBS, wherein the identified DBS comprises at least one sequence incrementor object;

b) obtain a next value (NextVal) from a sequence (V) of the identified database (DB) incrementing by n, wherein a next incremented value is to be returned as 1+n and 1+2n, and wherein ‘n’ corresponds to number of sites; and

c) generate the primary key (p) by a call sequence object for a next value which returns the sequence (V) of the identified database (DB).

9 . The system of claim 7 , wherein at least one incoming transaction is updated based on the primary key by:

a) extract a candidate primary key identified for update associated with at least one incoming transaction as Vx;

b) determine an originating database server (a route_site_ID) by the candidate primary key and a modulo function n; and

c) route the update associated with at least one incoming transaction to the originated database server from the client.

10 . The system of claim 7 , wherein at least one incoming transaction is updated based on the non-primary key by:

a) create a framework to accept non-primary key update Structured Query Language;

b) execute another select query on a local database server DBSn, to select one or more potential primary keys to be updated;

c) execute a modulo function on all selected one or more potential primary keys to obtain resultant Route_Site_IDs; and

d) route a transaction to respective Route_Site_ID and executing an update statement on respective DBS.

11 . The system of claim 7 , wherein the conflict associated with the parent and the child association of at least one data manipulation language (DML) operation is dynamically monitored by:

a) check if there is the integrity Foreign key Constraint error on at least one DML operation is inserted on the identified database server (DBS);

b) determine the primary key of a parent record (Ppk) and calculating an associated Re-Route Site ID;

c) reroute a transaction to a database server (DBSr) and calculating new PK for a child table; and

d) execute an insert on the DBSr with new PK.

12 . The system of claim 7 , wherein an addition of at least one database server by:

a) obtain a downtime to record a global system change number with a timestamp (T_Add) which are relayed to at least one database server (DBS);

b) assign a Site_Id n+1 to a new database server DBSn+1;

c) increment by value to generate a primary key which changes to n+1; and

d) monitor the timestamp(T_U) of record to be updated pre update present in the database server.

13 . One or more non-transitory machine-readable information storage mediums comprising one or more instructions which when executed by one or more hardware processors cause:

routing at least one incoming transaction associated with an application to at least one corresponding database server (DBS) by implementing a logic that integrates a geographical proximity mean value (GPMV) and a sender internet protocol (IP) affinity value (SIPAV), wherein the at least one incoming transaction associated with the application corresponds to a sequence of operations performed on at least one corresponding DBS, wherein at least one incoming transaction associated with the application is routed to a failover database server determined by a predefined logic, if at least one corresponding DBS is unavailable to accept traffic, and wherein the sequence of operations corresponds to at least one of (i) a read operation, and (ii) a write operation;

processing the at least one incoming transaction to extract at least one data manipulation language (DML) operation written directly by the application using a change data capture mechanism from a database and replicated to each DBS other than a DBS on which the application is directly applying a data through one or more incoming transactions;

generating a primary key by allocating at least one unique identifier if the at least one data manipulation language (DML) operation is inserted on an identified database server (DBS), wherein each primary key corresponds to a numeric value;

updating at least one primary key of a record from the at least one incoming transaction, wherein the record correspond to one or more attributes, and wherein the update corresponds to a change in the one or more attributes; and

dynamically monitoring if there is a conflict associated with a parent and a child association of at least one data manipulation language (DML) operation based on an integrity Foreign key Constraint error obtained by processing at least one inserted primary key on the record from the at least one incoming transaction;

wherein the GPMV corresponds to a score assigned to each potential route based on a geographical proximity, wherein a database server A receives a higher GPMV, if the database server A is closer to a destination than a database server B, wherein the SIPAV corresponds to a sender internet protocol (IP) which comprises an affinity value associated with each DBS, and wherein a traffic of each client is routed to closest available DBS based on proximity of the sender IP of each client to the identified closest available DBS.

14 . The one or more non-transitory machine-readable information storage mediums of claim 13 , wherein the primary key is generated by:

a) assigning, via the one or more hardware processors, a site identifier (Site_ID) to the identified DBS, wherein the identified DBS comprises at least one sequence incrementor object;

b) obtaining, via the one or more hardware processors, a next value (NextVal) from a sequence (V) of the identified database (DB) incrementing by n, wherein a next incremented value is to be returned as 1+n and 1+2n, and wherein ‘n’ corresponds to number of sites; and

c) generating, via the one or more hardware processors, the primary key (p) by a call sequence object for a next value which returns the sequence (V) of the identified database (DB).

15 . The one or more non-transitory machine-readable information storage mediums of claim 13 , wherein at least one incoming transaction is updated based on the primary key by:

a) extracting, via the one or more hardware processors, a candidate primary key identified for update associated with at least one incoming transaction as Vx;

b) determining, via the one or more hardware processors, an originating database server (a route_site_ID) by the candidate primary key and a modulo function n; and

c) routing, via the one or more hardware processors, the update associated with at least one incoming transaction to the originated database server from the client.

16 . The one or more non-transitory machine-readable information storage mediums of claim 15 , wherein at least one incoming transaction is updated based on the non-primary key by:

a) creating, via the one or more hardware processors, a framework to accept non-primary key update Structured Query Language;

b) executing, via the one or more hardware processors, another select query on a local database server DBSn, to select one or more potential primary keys to be updated;

c) executing, via the one or more hardware processors, a modulo function on all selected one or more potential primary keys to obtain resultant Route_Site_IDs; and

d) routing, via the one or more hardware processors, a transaction to respective Route_Site_ID and executing an update statement on respective DBS.

17 . The one or more non-transitory machine-readable information storage mediums of claim 13 , wherein the conflict associated with the parent and the child association of at least one data manipulation language (DML) operation is dynamically monitored by:

a) checking, via the one or more hardware processors, if there is the integrity Foreign key Constraint error on at least one DML operation is inserted on the identified database server (DBS);

b) determining, via the one or more hardware processors, the primary key of a parent record (Ppk) and calculating an associated Re-Route Site ID;

c) rerouting, via the one or more hardware processors, a transaction to a database server (DBSr) and calculating new PK for a child table; and

d) executing, via the one or more hardware processors, an insert on the DBSr with new PK;

wherein an addition of at least one database server by:

a) obtaining, via the one or more hardware processors, a downtime to record a global system change number with a timestamp (T_Add) which are relayed to at least one database server (DBS);

b) assigning, via the one or more hardware processors, a Site_Id n+1 to a new database server DBSn+1;

c) incrementing, via the one or more hardware processors, by value to generate a primary key which changes to n+1; and

monitoring, via the one or more hardware processors, the timestamp(T_U) of record to be updated pre update present in the database server.