CODEWITHSUNDEEP
javadatabase
janusz j
janusz j

Reputation: 329

Database synchronization in java

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.

Upvotes: 0

Views: 56

Answers (1)

CausingUnderflowsEverywhere
CausingUnderflowsEverywhere

Reputation: 2188

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.

Upvotes: 1

Related Questions