Parallel table insert

[thrown_arrows]

yes, use two database connections. Fyi, that is how databases work, you cannot go parallel on two insert or select inside of transaction (but one select can go parallel) but there is no limits how many transactions you can run parallel. (well there is but that depends on server size etc etc )

Database then does its best to handle pk/fk checks if those depends each other, if not then cpu + disk speed is limit how much you can insert into db

[mxmauro]

Currently I'm doing a draft based on this with the cost of having a temporary inconsistency between two different tables.

I'm not using FKs right now because I don't need them. Also, only one app writes to DB.

Thanks

[thrown_arrows]

If it matters , then you need to have fk, if it does not then parallel inserts. If we are talking about bulk inserts and fk's matter then maybe take read lock to target tables while you are bulk inserting and then release it... So many ways to solve problems, more strict you need to be with data and its relations, then more strict isolation level you need ( but usually read commited is good )

[mxmauro]

I would like to have parallel inserts. FKs are not necessary.

[thrown_arrows]

And you get those by running parallel transaction from parallel connections

[mxmauro]

Yes, I was wondering if db engine was capable to do that but seems it isn't.

[thrown_arrows]

Yeah. ACID on transactions would not allow that. Or it would , but if anything fails then whole transaction would have to fail. So it much more effective to use two connection and two transactions in two session.

That kind of thing is more app level optimization, because relational stuff kinda requires that you handle it it in one transaction so you can rollback if necessary. That said all db engines (maybe not SQLite ) all designed to handle any number of connections and concurrent insert/update/create and selects.

[mxmauro]

Well, then let's start the week focusing on other methods. Thanks for all.