Multiple threads writing into single database table

Question

i am having a issue with multiple threads inserting into a single table in database using hibernate. Every thread generates some data and then inserts them into a table. Problem is that inserting is processed by only 1 thread, because of database locks. What is the best workaround, so the threads can all insert in the same time? I tried to have 1 dedicated thread that will be writing into table and others will be generating data. But the data is generated way faster then inserted, so it doesn't solve my problem.

My only idea is to create own database/tables for each thread, but that seems kinda weird to me, because i don't know how many threads i will be creating in advance. Is there a better solution?

Probably Transactions. Because if you insert little bits you're limited to surprisingly low amounts of transactions per second regardless of threads. If you bundle data into bigger transactions you increase the throughput since that transactions per second limit doesn't care that much about how big the transaction is. docs.jboss.org/hibernate/orm/3.3/reference/en/html/batch.html More threads means more contention and that means degrading performance — zapl
– zapl, Commented Nov 21, 2015 at 11:43
Change database to Oracle, it creates redo logs and writes to it for each thread. — user2173738
– user2173738, Commented Nov 21, 2015 at 11:52
If you're generating data too fast for a properly set-up database to handle (meaning that you've appropriately tuned the hardware and journaling, and you're using only READ COMMITTED), then SQL is almost certainly not the appropriate data store. — chrylis -cautiouslyoptimistic-
– chrylis -cautiouslyoptimistic-, Commented Nov 21, 2015 at 12:22

Steephen · Accepted Answer · 2015-11-21 11:41:57Z

1

You have to keep a queue to keep data to be inserted. So all processing thread can insert data into queue. Once your inserting thread insert data into DB, you can remove it from queue.

answered Nov 21, 2015 at 11:41

Steephen

16.1k7 gold badges46 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2173738 Over a year ago

You are missing that the data is generated way faster then inserted, so even without code this comment doesn't solve a problem.

Steephen Over a year ago

@nikpon I am not missing it as you said. That is why I suggested a queue to handle generated data before inserting to DB.

Collectives™ on Stack Overflow

Multiple threads writing into single database table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related