Database synchronization in java

Question

I didn't choose yet the database, but I will have around 2 milions of rows with 2 columns. For each row I have to make computation and afterwards it should be marked as consumed (row should be deleted or another column of state should be updated). I don't know yet what is faster solution, but another threads should have concurrent access to this db and be able to check consumed rows and read another. I am thinking of SQLite as provider. Some pseudo code:

ExecutorService executor = Executors.newFixedThreadPool(7);
executor.execute(() -> {
    // access database
    // get state of last row (if locked get one before)
    // get string if not consumed
    // make computation
    makeComputation(string)}); // db cannot be locked here, as it takes around 1-2 sec.
   // update state or delete row

I don't know how to make transactions here to avoid sequential access to db or any storage. Perfomance is important.

I don’t think (and could be wrong) that SQLite provides threaded access. If you’re after a stand-alone/embedded db, my first port of call is always h2 - it’s a pure java db implementation, supports in memory, stand-alone (no server) and embedded configuration - it might be worth a look just to get you logic working — MadProgrammer
– MadProgrammer, Commented Nov 4, 2022 at 23:11
I suggest selecting a database first to help bring a focus to your question and more accurate answers. And the database will only have up to 7 concurrent connections? — CausingUnderflowsEverywhere
– CausingUnderflowsEverywhere, Commented Nov 5, 2022 at 1:33

CausingUnderflowsEverywhere · Accepted Answer · 2022-11-05 01:32:02Z

You can start by organizing your code into DatabaseTask objects which implement Runnable so you can toss them into the ExecutorService. They will be provided the information they need to target and work on a specific row.

Additionally you should create a class that finds the database work to do

Object databaseWriteLock = new Object();
ExecutorService executor = Executors.newFixedThreadPool(7);
executor.submit(new DatabaseTask(55)); //Build a class that deals with organizing and submitting database tasks to the thread pool in batches of 7.




public class DatabaseTask implements Runnable {
    private int databaseRowId;

    public DatabaseTask(int rowId) {
        this.databaseRowId = rowId;
    }

    @Override
    public void run() {
        try (Connection connection = Database.getConnection()) {
            // .. do the task for that database row
        } catch (SQLException e) {
            
        }
    }
}

public class Database {
    public static Connection getConnection() throws SQLException {
        return databaseConnectionPool.getConnection();
    }
}

You can use HikariCP as the java code that will interface with the database and deal with creating connections. It supports many different databases.

Depending on your underlying database choice, the database can provide synchronization. A common database is MariaDB using InnoDB table structure.

Collectives™ on Stack Overflow

Database synchronization in java

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related