VersionID in TSQLRecord

Firali · 2015-02-09 16:05:08

Hello,

I am interested in, that are you going to add some VersionID property to TSQLRecord class and check that, VersionID is same in database during updating record? or may be there is this functionality already?

In real application will be situation, when two user has retrived same record and both is updating data at the same time. After that one change will be cleared without notify to user.

Best regards.

ab · 2015-02-09 17:09:49

What you can do is "locking" the records before updating it.
You can set the optional ForUpdate: boolean to TRUE to most record retrieval methods (e.g. TSQLRest.Retrieval or TSQLRecord.Create) which will LOCK the record until TSQLRest.Update or TSQLRest.Unlock is called.

    /// get a member from its ID
    // - return true on success
    // - Execute 'SELECT * FROM TableName WHERE ID=:(aID): LIMIT 1' SQL Statememt
    // - if ForUpdate is true, the REST method is LOCK and not GET: it tries to lock
    // the corresponding record, then retrieve its content; caller has to call
    // UnLock() method after Value usage, to release the record
    // - this method will call EngineRetrieve() abstract method
    // - the TSQLRawBlob (BLOB) fields are not retrieved by this method, to
    // preserve bandwidth: use the RetrieveBlob() methods for handling
    // BLOB fields, or set either the TSQLRestClientURI.ForceBlobTransfert
    // or TSQLRestClientURI.ForceBlobTransfertTable[] properties
    // - the TSQLRecordMany fields are not retrieved either: they are separate
    // instances created by TSQLRecordMany.Create, with dedicated methods to
    // access to the separated pivot table
   function Retrieve(aID: TID; Value: TSQLRecord;
      ForUpdate: boolean=false): boolean; overload; virtual;

Firali · 2015-02-09 18:30:29

Thanks for reply.

Retrieve(ForUpdate = True) method has some disadvantages:

1: If you have already list of SQLRecords you must again retrieve data from server(needless network bandwidth).
2: If client application crashes record remained locked.
3: If you have two or more server application (e.g. for horizontal scaling), Retrieve(ForUpdate = True) is not working.

In case of "VersionID" all this disadvantages are solved.

Regards.

ab · 2015-02-09 18:43:12

1. You have the Lock() method for this.
2. There is a timeout on server side.
3. Such a configuration would probably won't scale well, from mORMot point of view, anyway.

The "VersionID" is in fact more or less what http://synopse.info/fossil/tktview?name=3453f314d9 propose.

Firali · 2015-02-09 20:46:28

1. Lock() does not checks if there was any update on this record before and the same problem(Previous change will be lost)
2. I think that there is small bug. TSQLLocks.PurgeOlderThan is called from TSQLModel.PurgeOlderThan and TSQLModel.PurgeOlderThan is not called from nowhere.
3. I am going to use MongoDB with StaticMongoDBRegisterAll. I think that there will not be any problems with two or more server applications if it will have functionality like "VersionID".

Firali · 2015-02-14 13:22:32

Hello,

Can you see why

ab wrote:

2. There is a timeout on server side.

does not works?

Regards.

ab · 2015-02-14 13:34:31

It works, but you have to call it by yourself, depending on your requisites.

Firali · 2015-02-14 13:58:17

It may be called from client application?

ab · 2015-02-14 14:47:12

The locks are local.
So you have to call TSQLModel.PurgeOlderThan() on both side, since each side (client or server) will have its own list of locks.

But the main purpose is to call it on server side, since most of the time, records may be locked by clients which became out of reach (e.g. after a broken connection).

It is possible that clients have still locked items, but in this case it sounds like a client code incorrect implementation.
If long standing locks are detected on the client side, you should correct your code ASAP!

In all cases, the best idea is to maintain the lock as short and safe as possible.
And the best pattern is to not use locks from the client side, but define some persistent services on the server side.
See for instance the CQRS persistent services we define in mORMotDDD.pas.

Firali · 2015-02-14 16:15:11

It is desired to have method in TSQLRestClientURI class like this

function Lock(aTable: TSQLRecordClass; aID: TID; aPurgeOlderThan: cardinal=30): boolean;

What do you think about it?

ab · 2015-02-15 07:08:19

As I wrote, client-side purging does not make sense.
It should never happen.
So I do not think it is a good idea to add such a parameter.
Just use aRestInstance.Model.PurgeOlderThan() directly.

Firali · 2015-02-15 09:41:18

This method must purge on server side, not on client side. Something like this

function TSQLRestClientURI.Lock(aTable: TSQLRecordClass; aID: TID; aPurgeOlderThan: cardinal=30): boolean;
var
  SendData: RawUTF8;
begin
  if (self=nil) then
    result := false
  else
  begin
    SendData := UInt32ToUtf8(aPurgeOlderThan);
    result := URI(Model.getURIID(aTable,aID),'LOCK',nil,nil,@SendData).Lo=HTML_SUCCESS;
  end;
end;

ab · 2015-02-15 13:22:45

IMHO the purge should definitively not triggered by the client side.
It is server side responsibility to purge any deprecated locks.

Firali · 2015-02-15 14:07:06

OK.

Is It right way to use timer to purge deprecated locks on server side?

ab · 2015-02-16 07:23:48

You may use a timer, but note that a server should not have any UI, so there should be no main thread nor Windows message loop in the server executable, so a timer could be perhaps not the best idea.
You could run it using a method-based service, or just override any virtual function of TSQLRestServer to add it.

Firali · 2015-02-16 20:28:28

OK, but there are some questions more...

For example, there are two client app and one server app. Logical steps is like this:

1. Client_1 locks some record.
2. Some process purges this lock on server side.
3. Client_2 locks same record.
4. Client_1 updates record.

Must Client_1 be able to update record?

mORMot Open Source

#1 2015-02-09 16:05:08

VersionID in TSQLRecord

#2 2015-02-09 17:09:49

Re: VersionID in TSQLRecord

#3 2015-02-09 18:30:29

Re: VersionID in TSQLRecord

#4 2015-02-09 18:43:12

Re: VersionID in TSQLRecord

#5 2015-02-09 20:46:28

Re: VersionID in TSQLRecord

#6 2015-02-14 13:22:32

Re: VersionID in TSQLRecord

#7 2015-02-14 13:34:31

Re: VersionID in TSQLRecord

#8 2015-02-14 13:58:17

Re: VersionID in TSQLRecord

#9 2015-02-14 14:47:12

Re: VersionID in TSQLRecord

#10 2015-02-14 16:15:11

Re: VersionID in TSQLRecord

#11 2015-02-15 07:08:19

Re: VersionID in TSQLRecord

#12 2015-02-15 09:41:18

Re: VersionID in TSQLRecord

#13 2015-02-15 13:22:45

Re: VersionID in TSQLRecord

#14 2015-02-15 14:07:06

Re: VersionID in TSQLRecord

#15 2015-02-16 07:23:48

Re: VersionID in TSQLRecord

#16 2015-02-16 20:28:28

Re: VersionID in TSQLRecord

Board footer