Hi,

I use SelfServicing and am working on some tables designed in 2004. In those days I used to have PK that partly consisted of counters. E.g. a Date and a Count. I wasn't using LLBLGen then but worked mainly with Stored Procedures in SQL Server. It was common practice to run sets of inserts / updates / deletes in a single transaction. You have to take good care of the order in which you did the changes. E.g. when you insert a record with the same Date and an existing Count (add something in a range), you woud first have to update the ones with a higher counter in a reverse order and then insert the new record. You would get a PK violation if you did it in a different order.

I have tried to implement this way of working in LLBLGen, but that failed. Probably because ADD is done before UPDATE. I would like the updates and inserts done in the order I added the entities to the Transaction. Is this possible?

You can't do this in a Unit of work as a UoW batches work together per task (insert, update etc.). You can schedule updates before inserts, but that's likely not what you want.

I think you should execute the work immediately, using a Transaction object to share the work inside a transaction, so add the entity/collection to the transaction, call save to save the entity, and that way you can control the work being done in the order you need.

Otis wrote:

You can't do this in a Unit of work as a UoW batches work together per task (insert, update etc.). You can schedule updates before inserts, but that's likely not what you want.

I think you should execute the work immediately, using a Transaction object to share the work inside a transaction, so add the entity/collection to the transaction, call save to save the entity, and that way you can control the work being done in the order you need.

Do you suggest I should use a single transaction to run all the work under? That is what I tried. The tables involved are in a parent-child relation. I will first try to make it work by directly doing the saves. If it works I will probly try to use the transaction again.

I think it'll be recipe for problems, no matter what you try... The thing is that you always have to lock the whole table to make sure the counter is uninterrupted. This is a fallacy, you can't achieve it. E.g. you insert and it fails for some reason, and another thread also does an insert and it succeeds. It's similar to inserting a pk based on 'the highest number in the table' +1.

the sequence for the counter is unique per date I recon, or unique per table? because if it's the latter, you can change the field to an identity field, set the sequence to start at the last value in the table and simply insert as normal.

Otis wrote:

I think it'll be recipe for problems, no matter what you try... The thing is that you always have to lock the whole table to make sure the counter is uninterrupted. This is a fallacy, you can't achieve it. E.g. you insert and it fails for some reason, and another thread also does an insert and it succeeds. It's similar to inserting a pk based on 'the highest number in the table' +1.

the sequence for the counter is unique per date I recon, or unique per table? because if it's the latter, you can change the field to an identity field, set the sequence to start at the last value in the table and simply insert as normal.

I did not want to change the tables because that would mean more changes in the software. It is a list of production orders for a single day. For that specific day there is only 1 list. It worked under a single transaction in SQL Server so I thought it would be possible to implement this in LLBLGen, assuming transactions in SQL Server and in LLBLGen are equal.

JayBee wrote:

Otis wrote:

I think it'll be recipe for problems, no matter what you try... The thing is that you always have to lock the whole table to make sure the counter is uninterrupted. This is a fallacy, you can't achieve it. E.g. you insert and it fails for some reason, and another thread also does an insert and it succeeds. It's similar to inserting a pk based on 'the highest number in the table' +1.

the sequence for the counter is unique per date I recon, or unique per table? because if it's the latter, you can change the field to an identity field, set the sequence to start at the last value in the table and simply insert as normal.

I did not want to change the tables because that would mean more changes in the software. It is a list of production orders for a single day. For that specific day there is only 1 list. It worked under a single transaction in SQL Server so I thought it would be possible to implement this in LLBLGen, assuming transactions in SQL Server and in LLBLGen are equal.

They are in a way, I was more referring to e.g. recursive saves of a graph as in that case you don't have influence over the order. If there are no related entities to be persisted, then you can just save when you have an entity ready to be persisted.

Is it possible to first commit all the updates and then commit the inserts, all within a single transaction? The updates have to be committed in the order they were added and then the inserts also in the order they were added.

Try using a UnitOfWork, and specify the working order. Please read the following documentation page

Walaa wrote:

Try using a UnitOfWork, and specify the working order. Please read the following documentation page

LLBLGen has its own logic to set the order in which updates in the database are done. When I tried using a UnitOfWork and added changed entities to that unit, the order in which the entities are updated is not the order in which they were added. That is not what I expected.

Now I am going to split it in 2 parts: 1. Create space and commit 2. Fill space and commit

I am not going to use UnitOfWork but just use a the plain Transaction object.

It works using the 2 transactions / commits.