Re: Replication and Locks

Hi James The response is after what you wrote. Thanks for your point of view. In <james.lawrence.62.000A58D3@EPAMAIL.EPA.GOV> james.lawrence@EPAMAIL.EPA.GOV writes: > >The flaw in the log approach is that if the process fails, >machine failure etc, then the state of the replicated >transaction is unknown. Oracle's approach, while incurring >more processing overhead, uses a true two-phase commit to >replicate the transaction and to record that the transaction >was successfully replicated. If a failure occurs the transaction >is rolled back and the data remains consistent, ie the >destination data has not been replicated and the source of >the data knows this. There is no additional locking overhead >in this approach. You are not addressing some of Oracle's biggest issues. Let's take a look at a customer with only 10 nodes. Further let's say that the transactions to be replicated involve only 30 tables. In Oracle's trigger based approach the CUSTOMER has to write the triggers, one for each of the involved tables of a given transaction, per node. So let's say that a transaction to be replicated involves 3 tables. That means that at a minimum the customer has to write at least 3 triggers, perhaps more depending on transaction complexity. That in itself is bad, but is not the worse of it. Now consider application maintenance, where most customer IS budget is spent. Since these triggers must exist on each involved table, on each node, then you are talking of at least 30 (and possibility more) triggers for a 10 node replication of a transaction involving 3 tables. If the customer needs to change a trigger, for whatever reason, then they have to change it in all 10 nodes. That leaves the customer open to customer reintroduced BUGS. Keep in mind, we are talking about a single transaction here! Imagine having hundreds of transactions involving hundreds of tables? As far as two-phase commit for replication...realize, that a two phase commit requires at least 5 network messages for only two nodes. The number of network messages required increases with the number of nodes: Time Message Issued Message Receiving Interval From Node Sent Node -------- -------------- ------------- ---------- t1 Coordinator Prepare Node 2 t2 Node 2 Ready Coordinator t3 Coordinator Commit Node 2 t4 Node 2 Commit OK Coordinator t5 Coordinator Done Node 2 The way two-phase commit recovery mechanisms work (including Oracle's) is that if the two-phase mechanism fails for any reason (network burp, server down on any node) all the involved data in all the nodes is locked. Now, have you ever tried to recover a failed two-phase commit in Oracle7? How does Oracle7 tell the DBA that a two-phase commit is hanging? Two-phase commit isn't perfect! Let's say that you have 5 nodes, half those of our first example: Time Message Issued Message Receiving Interval From Node Sent Node -------- -------------- ------------- ---------- t1 Coordinator Prepare Node 2 t2 Coordinator Prepare Node 3 t3 Coordinator Prepare Node 4 t4 Coordinator Prepare Node 5 t5 Node 2 Ready Coordinator t6 Node 3 Ready Coordinator t7 Node 4 Ready Coordinator t8 Node 5 Ready Coordinator t9 Coordinator Commit Node 2 t10 Coordinator Commit Node 3 t11 Coordinator Commit Node 4 t12 Coordinator Commit Node 5 t13 Node 2 Commit OK Coordinator t14 Node 3 Commit FAILED Coordinator t15 Node 4 Commit OK Coordinator t16 Node 5 Commit OK Coordinator Notice that in t14 Node 3 failed to commit successfully. Any number of things could have caused this: log corruption, head crash on drive having data being modified, power failure, etc. Notice that if a log corruption happens then it suffers from the same problems you pointed out the Sybase Replication Server may have. So a bad log has some negative affect on both technologies. Back to the recovery of the failed two-phase commit. The coordinator has to look at its log so that it can determine where to continue with the two-phase commit. One obvious question here is 'what is happening to the involved data in nodes 2, 4 and 5?' Is it really commited or is it still locked? The data should really be physically written, but the commit needs to remain 'hidden' from other users until the nodes receive the 'done' message from the coordinator. In essence what is happening here is that all the data in all the involved nodes is LOCKED until the two-phase commit transaction completes fully. Yes, Oracle DOES LOCK DATA IN REPLICATION since it uses the two-phase commit protocol. Oracle does not provide customers with an easy way of determining which node is the one that failed in a two-phase commit transaction. The DBA has to hunt this down manually by looking at system tables. How is the DBA notified of the problem? Some user calls and says I'm locked up! The DBA says what where you doing? Then you can imagine the rest. There are other things involved in failed Oracle replication transactions, but we will deal with these later. Well...I'll give you a hint: one has to do with the RECO process. But, I'll save that for later, in case one of my friends from my prior employer decides to respond to this. Now, back to the Sybase Replication Server. Given all the problems associated with two-phase commit, Sybase decided not to make two-phase commit the primary mechanism for replication. Sybase has supported two-phase commits since 1987 so we've learned a lot during the 6 years prior to releasing Replication Server in 1993 (the year Oracle started supporting two-phase commit). The replication of 'commited' transactions is much safer than that provided by two-phase commit systems like Oracle. By the time the Sybase Replication Server gets to the log all the data has been commited and locks have been released. All Sybase Replication Server does is read the transaction log stores it in its own log then forwards it (store and roll-forward). If any of the receiving nodes, such as node 3 in the example above, becomes unavailable the other nodes can continue to operate without having to wait for some DBA to resolve the problem. When the Sybase DBA determines what happened to node 3 (in the Rep Server scenario) they have the Sybase Replication Server roll-forward the transaction again. The bottomm line is that in Oracle's case if one node 1 of 10 nodes fail then the data on all 10 nodes is locked up, because of the two-phase commit. In Sybase's case then you have 1 node that is not up to speed on with the other 9, until the DBA intevenes, but the users can continue to operate on all 10 nodes. Is there no value in two-phase commit? Of course, there is. Sybase is not going to stop supporting a feature that its been supporting for 8 years. Two-phase commit is needed when customers require that all data@