mORMot Open Source

With my humble experience with Firebird, I want to point your attention to some earlier posts:

and:

So martin.suer noted that Firebird uses MVCC for concurrency control, not locking as other DBs. The word "Deadlock" from the error message should be read as "Conflict" in that context. As long as mORMot creates different connections for each thread, those conflicts will be inevitable.

On the other hand, for Firebird there is no such thing as DML outside a transaction, so I can't guess the effect of Zeos tiNone transaction isolation used in:

constructor TSQLDBZEOSConnection.Create(aProperties: TSQLDBConnectionProperties);
begin
  inherited Create(aProperties);
  ...
  fDatabase.SetTransactionIsolation(tiNone);
end;

Perhaps that is the reason for:

In the background process all DMLs are executed properly, but there is a need for an explicit Commit at all clients in order to see the changes if isolation is tiReadCommited or higher.

I guess that playing with  TSQLDBConnectionProperties.ConnectionTimeOutMinutes just triggers some implicit Commit and that is the reason everything to look fine at the surface. I suggest explicit StartTransaction/Commit to be used at the Client instead of changing the execution mode and the connection timeout property.

AFAIK the Oracle also uses MVCC and since MSSQL 2008 there is an option to run the server in either locking or MVCC mode. So this can be an issue on other RDBMSes too.

Regards,

All of them support double quotes. Not sure only for MS Access, but honestly, I don't wanna know

What's wrong with the case sensitivity?

Sounds reasonable.

@ab,
Since I'm not sure how I can be helpful, I'll remain available if you have more questions. Meanwhile, I'll try to get more familiar with the framework. Thanks!

Newbies confused! ON DELETE SET DEFAULT is a FK constraint, not trigger. Was the mORMot uses RDBMS DRI peculiarities to support the persistence? Isn't it all about to go beyond the RDB patterns?

More details on the journals...

A small excerpt from the journal:

key           siteid  tablename  recordkey     op stamp                   donorkey     donorsid
------------- ------- ---------- ------------- -- ----------------------- ------------ --------
52424390017   17      Subscript  52424380017   I  2014-01-27 10:32:33.187 52424390017  17
52424470017   17      Subscript  52424380017   U  2014-01-27 10:32:37.497 52424470017  17
52424490017   17      Subscript  52424480017   I  2014-01-27 10:32:43.420 52424490017  17
52424530017   17      Subscript  52424480017   U  2014-01-27 10:32:49.280 52424530017  17

Here key is the PK of the table, siteid is the identifier of the site where the CRUD operation occurred, tablename, recordkey are the row address where occured, stamp is the timestamp, donorkey is the key of the foreign journal table from where the change was imported, donorsid is the identifier of the site from where it was imported.

(each site has unique small number for site id, 'donor' is the foreign site)

On local updates donorkey equals key, donorsid equals siteid.

And the sync goes like this:

// check in our journal for last update from that site/table
SQL.Add( 'select coalesce(max(jlocal.donorkey),0)' );
SQL.Add( 'from journal jlocal ' );
SQL.Add( 'where donorsid = :dsid and tablename = ''' + tableName + '''' );
Params[0].Value := donorSiteID;
Open;
lastEntry := Fields[0].Value;

That is for retrieving the last update applied from the specified 'donor' for a given table.

And then:

SQL.Add( Format( 'select top %d', [recordsLimit]));
SQL.Add( '  j.key as "$$key", j.siteid as "$$siteid",');
SQL.Add( '  j.tablename as "$$table", j.recordkey as "$$rkey",');
SQL.Add( '  j.operation as "$$oper", j.stamp as "$$stamp",');
SQL.Add( '  j.donorkey as "$$donorkey", j.donorsid as "$$donorsid",');

// Here are the fields from the data row with the original field names
SQL.Add( '  l.*');

SQL.Add( 'from journal j left join "' + tableName + '" l on');
SQL.Add( '  j.recordkey = l.key');
SQL.Add( 'where');

SQL.Add( '  j.key > :lastentry and' ); // Newer journal entries
SQL.Add( '  j.donorsid <> :lsid and' ); // Not originating from here!
SQL.Add( '  j.tablename=''' + tableName + ''' and' ); // For requested table
SQL.Add( '  (j.operation = ''D'' or l.key is not null)');

// The keys are increasing so the order will be chronological
SQL.Add( 'order by j.key');

Params[0].Value := lastEntry;
Params[1].Value := localSiteID;

Open;

Now we have a dataset with the rows inserted, updated or deleted in the foreign table since the last sync. The reason to include 'D' operations is to process them in chronological order.

Next:

      while not EOF and (recordsLimit > 0) do
       begin
           case Operation of
             'I': InsertRec;
             'U': UpdateRec;
             'D': DeleteRec;
             'R': CheckInRec; // Insert or update?
           else
             raise EDatabaseError.Create('Unknown operation!');
           end;
         ...
         // Copy the foreign journal entry just processed into the local journal
         InsertJournalEntry; 
         ...

The InsertJournalEntry goes like this:

procedure InsertJournalEntry;
  begin
    with qryCloning do
    begin
      SQL.Text := 'exec dbclone_insert_in_journal :key, :siteid, :table, :rkey, :oper, :stamp, :donorsid';
      Params[0].Value := qryDonor.FieldByName('$$key');      // This value will be written in the donorkey column!
      Params[1].Value := qryDonor.FieldByName('$$siteid');
      Params[2].Value := qryDonor.FieldByName('$$table');
      Params[3].Value := qryDonor.FieldByName('$$rkey');
      Params[4].Value := qryDonor.FieldByName('$$oper');
      Params[5].Value := qryDonor.FieldByName('$$stamp');
      Params[6].Value := donorSiteID;
      ExecSQL;
    end;
  end;

The only trick is the usage of donorkey and donorsid values. Used that way there is no need for a separate table for keeping information about each remote site and the corresponding sync progress. Also, it keeps track for the changes propagation among the databases.

Hope no one got bored!

Regards,

@ab
I was engaged several months in implementing the replica in T-SQL+FPC. Nevertheless, I want to note why mORMot way should be easier. BTW, much or less as you said:

1. The REST transport layer is just perfect for the purpose. Previously restricted at TDS with no options;

2. The tracking can be implemented in Pascal, no need for T-SQL/P-SQL/x-SQL triggers and hacks for detecting replication sessions or filling default values on OID columns;

3. Engaging the change-sets into the memory/cache  can greatly reduce the row/table locks and thus improve performance;

4. It will be very easy to find the table dependencies via RTTI and make the right master-detail tables sorting instantly. Now the order is hard-coded and it is computed (tool) from the DB metadata during the planning stage;

5. The OID's can be computed without a DB round-trip for each row.

Regarding the 4).

I think that should be the natural way.

Regarding the 5).

Consider my T-SQL OID generator:

CREATE procedure dbo.dbclone_genkey
  @out_key bigint output,
  @in_increment bigint = 1
as
begin
  declare @sid bigint, @key bigint
  
  set transaction isolation level repeatable read
  set xact_abort on
  begin transaction
  
  select top 1 @sid = dbclone_id, @key = dbclone_genval 
  from dbclone_siteid order by dbclone_ord
  
  update dbclone_siteid
  set dbclone_genval = dbclone_genval + @in_increment
  where dbclone_id = @sid
  
  commit transaction
  
  set @out_key = @key * 10000 + @sid;
end

Here we have @sid for a DB site identifier and @key for the next key to be returned, which must be modified and written back. This is our bottleneck. All CRU's must go through that procedure. The solution is to pre-allocate a range of ID's with the @in_increment parameter and to have just one round-trip for a block. This is not possible in pure T-SQL, but absolutely feasible at the mORMot OPF level.

I'll continue to feed you with the details, at least until you say it is enough .

And WOW! You're lightning fast with the changes!

Two doubts:

1. I don't think the timestamps are good, imagine nodes in different timezones, DST transitions, somebody changed the date, etc. Better is to rely on the monotony of the ID generator;

2. Into the RDB design and when normalizing, the number of tables tends to increase. This makes the change "impact" area smaller and gives less probability for conflicts. When de-normalizing, the entire aggregate will be contained in a single row and a slightest change will induce conflict; Imagine a proforma invoice with two rows, in a classic RDB the rows will be in a secondary table, when different rows are modified on different nodes then everythng will be ok, but when denormalized, the rows will be contained into the proforma and modifying any of the rows will mark the entire document as modified;

Personally, I don't like the attributes at all. I have a long experience with the .NET Frameworks (by necessity) and my opinion is that when somebody have no idea how to express something then he "invents" the attributes.

OK, got it!
Long time ago, when I was a student, someone said "The one who undertakes a merge replication is just looking for trouble...". So we are

Approx. numbers as I recall: total number of rows: 6M+, synchronization performed on 3-8 min. with mutual prime number of minutes for different nodes, about 100-150 rows max on a single synchronization, data ownership is implicitly defined by the application itself (three levels are actually different in their functionality). ACID is not expected at 100% but every violation must have a clear manifestation.  Conflicts are resolved by preselection who will be the winner  from the two participants.

For the feature request: http://synopse.info/fossil/tktview?name=3453f314d9

I think the simplest way to implement such a master/slave synchronization is to include a table-wide version number column, VN, no need for TModTime,TCreateTime. Every insert initializes the column in the inserted row with the max(VN)+1, every update also updates it into the updated row to max(VN)+1. The rows for the next synchronization then can be simply selected from the master as those with VN greater than the max(VN) from the slave table. The trouble is the deletion, but it can be either resolved by marking rows as deleted rather than actually deleting'em (and setting VN to max(VN)+1) or making a side "tombstone" table with the PK's of the deleted rows. It is not a big trouble, as long a surrogate (not a natural PK) is used which is guaranteed to be "stable".

The peer replication case:

It is crucial to have a way to uniquely address a row across multiple databases, so a unique value (among all nodes) must be assigned for each inserted row. It may not be the PK, but if the PK is auto-increment value, it may happen that the same value is already used in the other DB and that will lead to inevitable conflict. So, it will be best if we can use that "address" column as a PK also. Furthermore, for the reasons mentioned before, it will be also good for the generated unique values to have additional properties, e.g. monotony.

In a true RDBMS case, each CRUD operation is registered through a corresponding trigger. The registration is made in a side table, lets call it "journal" table. Each row is consisting of: The name (or some id) of the table, the type of the operation (Insert, Update, Delete), the unique "address" of the affected row, and finally the originating node id of the operation. The last thing is for distinguishing the local changes from external ones.

The synchronization goes as follows: for each synchronized table (the order is important, more on that later) the greatest "unique address" of external change is taken and rows with the greater value extracted from the other side journal. They should describe the latest changes from the other side. These rows left-joined with the corresponding table gives the complete data set to be INSERT OR UPDATEd, deletes have NULLs in their data columns. Of course this is the case when the unique "addresses" are ordered in the time domain, otherwise the comparison must be made on an additional timestamp field. The same thing must be repeated backwards to complete the table synchronization. It is also important to transfer the foreign journal entries into the local journal to mark the changes as "already applied".

Some difficulties exists, one of them is the FK enforced DRI. By the way, during the replication MSSQL disengages most of the constraints and triggers with NOT FOR REPLICATION directive.  Our solution is to synchronize the tables in strict topological order, with master tables first, then the slave tables. This will work when no loops exist into the FK constraints, also will work for a simple loop into the same table since the modifications are timely ordered. Will not work when longer loops exist, but I haven't seen so far such an "exotic" DB schema. There is a tool developed which extracts the dependencies between the tables and does the topological sorting.

Other difficulty is the initial snapshot of already operational DB, the simplest way to do this is to perform a backup and then restore it on the other node. Alternative approach is to define additional operation in addition to the I,U,D, e.g. Register and to perform that operation on all rows in the entire database. On the other side all such operations will be treated as INSERT OR UPDATE and thus (taking much mooore time) the entire content will be transferred. Of course, on an unprepared DB the row insertion order is unknown and the circular dependencies can be a real problem. The advantage of the latter approach is that the source DB doesn't go offline.

But after such a prolonged writing, the introduction of the  "shared nothing" principle discards all the concerns mentioned above

Regards,

It will take me some time to browse the links, but I'll try to give some quick explanation. I'll presume that the requirement of global unique identifiers is beyond doubt when it comes to multi-master replication (right?).

All DB engines use some data structures at the file level. MSSQL, Firebird, SQLite stores the tables in B-trees. MSSQL calls it "Clustered index". When using GUID as a PK, the B-tree pages are organised according the GUID values which are not monotonic. Usually, most of the tables follow some temporal dependence. Log tables are the most obvious example - they are always appended at the end (with regard of time). Besides that, usually the log tables doesn't have other PK than surrogates. It finally happens that your biggest table have the worst structure which dramatically reduces the data throughput. Even more annoying is that the log tables are usually queried for a specified period of time - using secondary index actually makes the server to engage most of the file pages in memory, because the rows were physically spread across much more pages than they need to. That turns a simple report into a nightmare. They later introduced a monotonic NEWSEQUENTIALID() to overcome this deficiency. See Good Page Splits and Sequential GUID Key Generation.

The lack of the monotonic property is the main trouble with the GUIDs.

Another problem

Further

At this point I'm concerned about the mORMot multiple caches and the performance penalties imposed invalidating them. It will be best if the replication system is coherent with the caches.
PostgreSQL replication was something in incubation at the time we started the implementation, I'll return back to reading to see the current status.

I'll investigate this suggestion, but not quite sure that Int32 will suffice for my needs. If we assume that nodes will be numbered 0-127, that makes 7 bits and leaves just 24 (signed) for the increasing part. 16M rows can be easily achieved taking in mind that the generator is the same for all tables.

One working implementation is with 2 levels of tree-like filtered replication; one central node, 5 middle-level nodes and 12 leaf nodes, total of 18. The central node collects the events from all leaf nodes and there the number of rows goes critical. Of course all that is subject to revision/redesign.

Regards,