Transaction details

[OliverUv]

Hello!

I've got a few questions about transactions. Assuming that mysql is used, what transaction level is used?

The documentation specifies

Wraps given function execution (and all operations made there) in a transaction. All database operations must be executed using provided repository. 

So we are not allowed to use repositories that operate on tables unrelated to the one initiating the transaction? What's the failure mode if we do this? Do we get some runtime error, or will things fail on the DB side?

With sequelize there are similar problems of not being able to use a connection pool with transactions. We currently establish a pool of connections to the database, where no transactions are used, and have a separate connection that is used for any transactional queries. This way we don't block the other connections from being used while the transaction is being run, while the DB will take care of invalidating the transaction if necessary.

Would a similar setup be usable with TypeORM? (Though it seems like we'd need to create our own connection pool abstraction on top of the ConnectionManager).

Cool project, hope it can fit our requirements.

[pleerock]

Hi,

If you don't use transactions then connection is opened per each operation you execute via repository/manager, for example findOne, findMany, persist, remove each of them takes one connection from pool, uses it and releases once its done.

If you are using transaction, it provides you a single instance of EntityManager which contain all operations above, and each such operation uses single connection provided by entityManager created by transaction, not connection per operation as before.

Transaction is opened and used within a single database connection retrieved from connection pool.
You should be able to use other connections from the pool at the same time. You should be able to use other connections and open transaction on each of them separately, each of them should be able to work separately.

At least this is how I think I designed this system. Try to create a simple example and if it will not give you behaviour I described, then show me the code with result and expected result and I take a look whats the problem and let you know if its a bug or expected behaviour based on some technical reasons.

[OliverUv]

Ok! I'll do some testing, thanks.

Any thoughts on allowing persistent connections? Constantly closing and opening the connections in the pool seems inefficient for large scale systems.

If not, do you think it would be difficult for us to implement this functionality ourselves (either providing the feature upstream to you, or adding some API option that allows us to provide this functionality locally)?

[pleerock]

Constantly closing and opening the connections in the pool seems inefficient for large scale systems.

but how often they should be opened and closed, based on what / what events? And how do you want to control this? I need more details about requested solution.

do you think it would be difficult for us to implement this functionality ourselves (either providing the feature upstream to you, or adding some API option that allows us to provide this functionality locally)?

probably this depend on you and functionality you need. I still don't know what functionality you need, I need more details to provide more info.

[OliverUv]

Currently we open the connections and keep them for as long as possible, so that there is no handshake delay when we get requests. I suppose they close when they time out, or when we restart the web server while updating it.

[pleerock]

But it is already solved by connection pool, isn't it? It already opens connections for you and gives you them, and don't close them.

[pleerock]

I hope I answered your questions.

[OliverUv]

@pleerock Hi sorry yes, sorry I didn't get back to you. We've started using TypeORM with mixed feelings about it. Some good some bad. To try to be constructive, here are some general things I've thought about:

• The QueryBuilder stuff is a bit too stringly-typed for my taste.
• No way of specifying isolation levels for transactions (either individual or for all transactions for a connection/storage)

Of course I understand that fixing either of these would be big tasks.

We'll keep using it, I'm impressed by the constant pace of progress. Hope to see continued improvement as time passes!

[pleerock]

Hope to see continued improvement as time passes!

for sure!

The QueryBuilder stuff is a bit too stringly-typed for my taste.

I did not get this, can you please tell more about it?

No way of specifying isolation levels for transactions (either individual or for all transactions for a connection/storage)

specifying isolation levels is something I have in plans, would be great if you create a separate issue and tell how do you see it to work in typeorm

[WoLfulus]

We're missing isolation levels too. We've hit a wall with this one :(

[pleerock]

would be great if you create a separate issue and tell how do you see it to work in typeorm

[chrishenx]

Hey @pleerock , does Promise.all([dbOperation1, dbOperation2, ...]) within a transaction retrieved by manager.transaction() runs concurrently? Meaning all queries are sent to the database server at once?

[pleerock]

@chrishenx yes, currently they are. However I would recommend to send them in a queue (at least in some databases it might matter, in others it doesn't affect anything at all).

[chrishenx]

@chrishenx yes, currently they are. However I would recommend to send them in a queue (at least in some databases it might matter, in others it doesn't affect anything at all).

Awesome, this is good info. I think PostgreSQL, for example, only runs a query at a time for a single connection.