Suppose I have the following structure: A -> B (n:1) so the tables would be: A (id int, b_id int, name varchar) B (id int, name varchar)

When fetching a collections of As, and prefetching their Bs, shouldn't LLBLGen wrap the two queries in a transaction of at least 'repeatable read' isolation level? Otherwise, what's preventing the following sequence:

Connection 1: read A Connection 2: change A.b_Id and delete the former B Connection 1: read B where B.id == A.b_id err! B is missing?

I'm currently LLBLGen on a MySql database and see no transaction. Is it a) a feature that I don't understand, b) because or MySql, c) else?

Connection 1: read A Connection 2: change A.b_Id and delete the former B Connection 1: read B where B.id == A.b_id err! B is missing?

Do you want Connection 1 to read the old deleted B? Or do you want it to read Entity A again with the new Entity B

A Transaction only makes sense when a RollBack scenario is needed.

Walaa wrote:

Connection 1: read A Connection 2: change A.b_Id and delete the former B Connection 1: read B where B.id == A.b_id err! B is missing?

Do you want Connection 1 to read the old deleted B? Or do you want it to read Entity A again with the new Entity B

A Transaction only makes sense when a RollBack scenario is needed.

Since the two queries issued on Connection 1 come from the same adapter.FetchEntity() call, all I really want is the function to return entity A in a consistent state. I don't care if this state is the one before it is changed by Connection 2, or after, as long as the adapter manages this by itself.

As far as I understand it, if Connection 1 wraps its two queries in a 'repeatable read' transaction, then reading entity A will force the old B to remain readable within the transaction.

Without a transaction... I don't know what happens. Would the adapter throw an exception?

Fetches aren't done in an implicit transaction, i.e.: if you don't start a transaction, there's no transaction started. If you want to fetch them in one transaction, you have to start one, and make sure you use innodb on mysql otherwise you won't get any transaction at all (besides the fact that mysql and transactions are still not that big friends).

The scenario you described is possible and you can't prevent it. The thing is: the delete can also happen right AFTER you've fetched the data. The data in the db is then different from what you have in memory. This is ok, as by definition when you read data from a db, the data is 'stale', i.e.: the data in memory can differ from the data in the database. That's also why concurrency control is used to update data in the database again: because stale data changed in memory can undo changes made by a different thread/connection. If you want to block other connections, you make your database single user, which isn't that great for performance

Otis wrote:

Fetches aren't done in an implicit transaction, i.e.: if you don't start a transaction, there's no transaction started. If you want to fetch them in one transaction, you have to start one, and make sure you use innodb on mysql otherwise you won't get any transaction at all (besides the fact that mysql and transactions are still not that big friends).

That answers my question, many thanks!

Otis wrote:

The scenario you described is possible and you can't prevent it. The thing is: the delete can also happen right AFTER you've fetched the data. The data in the db is then different from what you have in memory.

Considering it is a web application, locking is not an option. Saving data will have to rely on some sort of optimistic concurrency control. That's OK with me.

Yet I still have to be careful and ensure that the data is read consistently. Either by wrapping fetches in a transaction, or by... don't know yet.

And just to make things clear: recursive Saves are done in an implicit transaction, aren't they?

And just to make things clear: recursive Saves are done in an implicit transaction, aren't they?

Yes they are. Similar question: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=5483