How to use Multi table transaction

fubail@qq.com · 2017-01-13 04:21:37

How to use Multi table transaction In the Master/Detail Situation?
I found TransactionBegin/BatchStart both need a TSQLRecordClass parameter, Is that means it supports one table transaction only?
I plan to use servcies, implement transaction on server side.How can I do?

edwinsn · 2017-01-13 07:14:13

Pass "nil" for the record class parameter to enable multi table transaction.

Note, if you are using client-side trans, it's strongly recommended to use the batch-mode instead.

fubail@qq.com · 2017-01-13 08:33:40

edwinsn wrote:

Pass "nil" for the record class parameter to enable multi table transaction.
Note, if you are using client-side trans, it's strongly recommended to use the batch-mode instead.

thanks. I'll try this.

zed · 2017-01-13 12:43:17

edwinsn wrote:

Note, if you are using client-side trans, it's strongly recommended to use the batch-mode instead.

Why?

ab · 2017-01-13 15:56:04

RTFM

fubail@qq.com · 2017-01-22 07:46:10

Sorry. I still have some problems. I am using server-side transaction. I tried two ways, both have problems.

aRestServer.TransactionBegin(nil, 0);
try
  aRestServer.Add(aMasterRecord, True);
  aDetailRecord.MaserId := aMasterRecord.AsTSQLRecord;
  aRestServer.Add(aDetailRecord, True);
  aRestServer.Commit(0);
except
  aRestServer.RollBack(0);
end;

In this way i can get and set the MasterId property.
But After aRestServer.Add(aMasterRecord, True), data has been written into database already. Commit and RollBack won't do nothing.

aSQLRestBatch.Add(aMasterRecord, True);
aSQLRestBatch.Add(aDetailRecord, True);
aRestServer.BatchSend(aSQLRestBatch, Res);

In this way, transactions works fine. But I don't konw how to set aDetailRecord.MasterId property.

The real problem is that I want use transactions in the Master/Detail situation, and set Master/Detail relationship correctly.
Any help?

lele9 · 2017-01-22 08:12:03

Hi,
Why don t use array of record (detail) as tsqlmasterrecord s property?

edwinsn · 2017-01-22 08:25:40

@fubail@qq.com,
I think you at least have two options:
1 - As @lele9 said, re-design your data model, make the "detail table" as a property of the master record, in this case I prefer TObjectList as the type of the property, that way, the "detail records" are stored in JSON in the db, you can then take advantage of the SQLite's JSON feature. If you use another db system, I believe most modern db system has support for JSON. ref: (https://synopse.info/files/html/Synopse … ml#TITL_26)

2 - Control the generation of the ID value for your master records.

fubail@qq.com · 2017-01-22 08:33:24

lele9 wrote:

Hi,
Why don t use array of record (detail) as tsqlmasterrecord s property?

It's not a new project. It should works fine with old data.
And this way is not so SQL, is it？

fubail@qq.com · 2017-01-22 09:02:41

@edwinsn
Thanks, I can try option 2.
I want to have an easier way. How do you solve this problem?

ab · 2017-01-22 09:30:01

@edwinsn
About data sharding and nesting object lists, we tend to use T*ObjArray, which are freed by the framework automatically, and also stored as JSON.

@fubail
It is in my todo list to add reference of IDs generated during the batch.
For instance, having TSQLREstBatch.Add returning a fake negative ID for the master, which will be recognized when the detailed record is inserted, and replaced by the actual real just-created ID.
But it is not implemented yet.

fubail@qq.com · 2017-01-22 09:59:46

@ab
Thanks. It's a good news.

edwinsn · 2017-01-22 10:45:52

I'm currently using a timestamp-based UUID generated at client sides, so far so good: https://synopse.info/forum/viewtopic.php?id=2522

@ab,

That's a great proposal! And I'm looking forward to it.

Last edited by edwinsn (2017-01-22 10:46:29)

AOG · 2017-01-22 11:42:02

ab,
+1 !!

ab · 2017-01-22 11:44:58

For ID generation, you have the TSynUniqueIdentifierGenerator class which was designed for that.
In respect to your Generate64BitUUID, it doesn't use random, so is much safer, and has a process ID, so you can generate unique IDs among several processes/services.
And it is also faster.

In production, for cases with a batch insertion with linked IDs, we use TSynUniqueIdentifierGenerator.
And it also fits very well the need of gathering some information from several uncoupled nodes into a shared MongoDB bigdata storage, as we designed.
You can obfuscate the ID into some hexadecimal value: we use it for public HTTP URI generation, to safely return some resources (like photos or videos) by convention.

mORMot Open Source

#1 2017-01-13 04:21:37

How to use Multi table transaction

#2 2017-01-13 07:14:13

Re: How to use Multi table transaction

#3 2017-01-13 08:33:40

Re: How to use Multi table transaction

#4 2017-01-13 12:43:17

Re: How to use Multi table transaction

#5 2017-01-13 15:56:04

Re: How to use Multi table transaction

#6 2017-01-22 07:46:10

Re: How to use Multi table transaction

#7 2017-01-22 08:12:03

Re: How to use Multi table transaction

#8 2017-01-22 08:25:40

Re: How to use Multi table transaction

#9 2017-01-22 08:33:24

Re: How to use Multi table transaction

#10 2017-01-22 09:02:41

Re: How to use Multi table transaction

#11 2017-01-22 09:30:01

Re: How to use Multi table transaction

#12 2017-01-22 09:59:46

Re: How to use Multi table transaction

#13 2017-01-22 10:45:52

Re: How to use Multi table transaction

#14 2017-01-22 11:42:02

Re: How to use Multi table transaction

#15 2017-01-22 11:44:58

Re: How to use Multi table transaction

Board footer