Shailesh, Sqlite does not have a 2 phase commit mechanism across hosts. In fact there is no way in sqlite currently to perform a transaction against two db's on two seperate hosts (unless of course the db's are on a shared filesystem).
1. Put the db's on a shared filesystem. In that case you'll need to be carefull of locking. There are known issues with NFS file systems that have incorrect locking mechanisms. 2. Implement your own 2 phase commit logic. Use a logging table where you would put the undo for the transaction. Then commit both db's and validate that once the commit succeeds on both systems you may purge the undo data. Or if it fails you'll need to apply the undo data back on the db's tables. You could base the undo/redo on: http://www.sqlite.org/cvstrac/wiki?p=UndoRedo Good luck. Ken Shailesh Madhukar Birari <[EMAIL PROTECTED]> wrote: Hello, Ken you are partially correct on what I am trying to implement. There are two hosts A & B, each having its on copy of databases dbA and dbB. A commit of a transaction means that it committed on both dbA and dbB. At any given point of time, both the databases should be exact replica of each other. If it cannot be done, is there a way that I can rollback committed operation in sqlite? Since in my case, it might happen that a commit on B succeeded but a commit of A failed and then I want to rollback the committed operation on B. Or is there a better way to achieve this functionality using sqlite. Kindly let me know, Regards Shailesh Date: Mon, 10 Mar 2008 08:15:30 -0700 (PDT) From: Ken Subject: Re: [sqlite] Two Phase commit using sqlite To: General Discussion of SQLite Database Message-ID: <[EMAIL PROTECTED]> Content-Type: text/plain; charset=iso-8859-1 Hello Shailesh, Seems like you may be trying to put a square peg into a round hole. Sqlite is an embedded database. Meaning it is not "distributed" as such unless you build the distribution and 2 phase commit logic yourself. Let me just clarify for a moment, by distributed you mean. a. 2 databases and 2 host platforms b. A trasnsaction that originates on host A but uses host b c. A transaction that upon commit on host a implies a commit on host b. d. A transaction that upon error of commit logic, host B will enter its transaction as "in doubt". e. A transaction that is rolled back on host A will also rollback on host b. However, Sqlite does have the capability to have two databases. You can attach a database and run a transaction/rolllback. But I'm not certain that it could recover from a failure when the databases reside on seperate filesystems (nfs/san/nas etc) and the DB is utilized by Host B. Consider this: host A houses A.db and host b houses B.db. B.db is accessible to both host a and host b (stored on a shared file system, but host A.db is stored locally ) A process on Host A opens A.db and Attaches B.db, opens a transaction and modifies data such that writes occur to A.db and B.db (ie journal files are created and modified data is written out). Then Lets say Host B becomes unavailble. Then a Process on Host B attempts to utilize B.db.... I think you'll get a failure of corruption of some form at this stage since B.db is involved in a transaction, but the journal file is not available for a recovery. Hope that helps. Ken Shailesh Madhukar Birari wrote: Hello, I want to implement a distributed database using sqlite. Can some one give me information of whether SQLite supports two phase commit? Thanks, Shailesh. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
