How to work with multiple connections to a single database?

Question

I'm new to stack overflow and I am currently using NetBeans with java to make a desktop app where multiple users can access one database at the same time. But unfortunately, I tried several codes enabling row-level locking and TRANSACTION_READ_COMMITTED which is stated in db.apache.org, but it didn't work.
I'm currently using an Embedded database in derby, and by searching the internet, I found this db.apache.org enter image description here, and this enter image description here.

I couldn't understand how to set multi-user database access by enabling row-level locking and TRANSACTION_READ_COMMITTED.

I tried this code, where I implement TRANSACTION_READ_COMMITTED by importing

import static java.sql.Connection.TRANSACTION_READ_COMMITTED;

and

con.setTransactionIsolation(TRANSACTION_READ_COMMITTED);

to set an isolation to TRANSACTION_READ_COMMITTED.

This is the my program.

`

import java.sql.*;
import javax.swing.*;
import java.util.logging.*;
import static java.sql.Connection.TRANSACTION_READ_COMMITTED;

public class Connect_data extends javax.swing.JFrame {
    
    String temp_use, temp_pass, temp_usertype, n, s , u;
    //FROM ACCOUNT
    
    Connection con;
    Statement stmt;
    ResultSet rs;
public void DoConnect(){

        try{
    
        //Connect TO THE DATABASE
    
        String host = "jdbc:derby:C:\\DATABSE_SUB\\VERe";
        String uName = "josh";
        String uPass = "1234";
        
        
        con = DriverManager.getConnection(host, uName, uPass);
        
        stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
    
        String sql = "SELECT * FROM ADD_BTN";
        
        rs = stmt.executeQuery(sql);
    
    
        con.setTransactionIsolation(TRANSACTION_READ_COMMITTED);
    
        }catch(SQLException err){
            JOptionPane.showMessageDialog(Connect_data.this, err.getMessage());
        } 
    }
}

`

What I'm missing at is enabling row-level locking, I honestly don't know how to implement this. I tried putting this

-- database-level property
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(
    'derby.storage.rowLocking', 'true')

in the execute command of the database services NetBeans but it didn't work.

When I run the program it connect to the database, but when I run the second program it gets an error, this is what I get an output.

Exception in thread "AWT-EventQueue-0" java.lang.SecurityException: sealing violation: package org.apache.derby.security is sealed
    at java.base/jdk.internal.loader.BuiltinClassLoader.getAndVerifyPackage(BuiltinClassLoader.java:906)
    at java.base/jdk.internal.loader.BuiltinClassLoader.defineOrCheckPackage(BuiltinClassLoader.java:877)
    at java.base/jdk.internal.loader.ClassLoaders$AppClassLoader.defineOrCheckPackage(ClassLoaders.java:211)
    at java.base/jdk.internal.loader.BuiltinClassLoader.defineClass(BuiltinClassLoader.java:849)
    at java.base/jdk.internal.loader.BuiltinClassLoader.findClassOnClassPathOrNull(BuiltinClassLoader.java:760)
    at java.base/jdk.internal.loader.BuiltinClassLoader.loadClassOrNull(BuiltinClassLoader.java:681)
    at java.base/jdk.internal.loader.BuiltinClassLoader.loadClass(BuiltinClassLoader.java:639)
    at java.base/jdk.internal.loader.ClassLoaders$AppClassLoader.loadClass(ClassLoaders.java:188)
    at java.base/java.lang.ClassLoader.loadClass(ClassLoader.java:520)
    at org.apache.derby.jdbc.AutoloadedDriver.connect(Unknown Source)
    at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:681)
    at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:229)
    at New_Pack.Connect_data.DoConnect(Connect_data.java:132)
    at New_Pack.Connect_data.jButton1ActionPerformed(Connect_data.java:624)
    at New_Pack.Connect_data$7.actionPerformed(Connect_data.java:580)
    at java.desktop/javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1972)
    at java.desktop/javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2313)
    at java.desktop/javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:405)
    at java.desktop/javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:262)
    at java.desktop/javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:279)
    at java.desktop/java.awt.Component.processMouseEvent(Component.java:6626)
    at java.desktop/javax.swing.JComponent.processMouseEvent(JComponent.java:3389)
    at java.desktop/java.awt.Component.processEvent(Component.java:6391)
    at java.desktop/java.awt.Container.processEvent(Container.java:2266)
    at java.desktop/java.awt.Component.dispatchEventImpl(Component.java:5001)
    at java.desktop/java.awt.Container.dispatchEventImpl(Container.java:2324)
    at java.desktop/java.awt.Component.dispatchEvent(Component.java:4833)
    at java.desktop/java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4948)
    at java.desktop/java.awt.LightweightDispatcher.processMouseEvent(Container.java:4575)
    at java.desktop/java.awt.LightweightDispatcher.dispatchEvent(Container.java:4516)
    at java.desktop/java.awt.Container.dispatchEventImpl(Container.java:2310)
    at java.desktop/java.awt.Window.dispatchEventImpl(Window.java:2780)
    at java.desktop/java.awt.Component.dispatchEvent(Component.java:4833)
    at java.desktop/java.awt.EventQueue.dispatchEventImpl(EventQueue.java:773)
    at java.desktop/java.awt.EventQueue$4.run(EventQueue.java:722)
    at java.desktop/java.awt.EventQueue$4.run(EventQueue.java:716)
    at java.base/java.security.AccessController.doPrivileged(AccessController.java:399)
    at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:86)
    at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:97)
    at java.desktop/java.awt.EventQueue$5.run(EventQueue.java:746)
    at java.desktop/java.awt.EventQueue$5.run(EventQueue.java:744)
    at java.base/java.security.AccessController.doPrivileged(AccessController.java:399)
    at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:86)
    at java.desktop/java.awt.EventQueue.dispatchEvent(EventQueue.java:743)
    at java.desktop/java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:203)
    at java.desktop/java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:124)
    at java.desktop/java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:113)
    at java.desktop/java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:109)
    at java.desktop/java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:101)
    at java.desktop/java.awt.EventDispatchThread.run(EventDispatchThread.java:90)

I was expecting this will workout but I couldn't find the answer. I need help.

Answer 1

Multiple issues here:

The primary

You have derby on the classpath twice. Fix that.

Secondary

derby is barely maintained, don't use it. More to the point, databases can manage multiple simultaneous connections, but not when you use it like this, which is an 'in-process' setup where the JVM itself becomes the db server. Any other app that runs simultaneously on the same hardware / using the same underlying files as data store is fundamentally not possible - only one 'database engine' can run at the same time. This means you can do this if there's just 1 JVM that hosts multiple connections, but not if your intent is to start more than one JVM and have them sync up with each other using the db. The solution is to run an actual DB server separately. If you're doing that, you might as well pick a good database engine such as postgres.

If it has to be in-process, hsqldb or h2 are all-java DB engines that are more convenient. If you run in-process, you still can't run 2 JVMs using the same backing datastore, you have to have a separate 'server' (h2 can operate as a server too if you want it to).

The tertiary

DBs do not work this way, at least, no DB engine I know of. You run 1 db engine per 'data store', you can't launch 2 separate processes each running DB queries off of the same source data files. That is not what transactions are for, and not what isolation levels configure. Maybe there's a DB engine out there that uses the file system or a pipe or a local TCP/IP connection to communicate the details, but without such a 'comms pipe', this cannot possibly work.

Hence, you need 1 java process (or non-java process, postgres would work too of course) to be a db engine, and then 1 java process each for every client app running, which connects to the db engine. This is complicated to set up right (I'm not aware of any libraries that will launch a db engine if it is not running and tear it down if needed afterwards for example). You could still use derby or h2 or hsql to host 'all java, in-process db engines', but you need to check if another java process is already using the db, and have a setup where you launch a localhost-responding-only TCP/IP server socket so that the other apps can connect to the first app, so that the first app is the only app that touches the DB files directly. This is all very very complicated, and I'm not aware of many libraries for this concept. Probably because 'java desktop app' is rare, and 'java desktop app intended to be run more than once simultaneously' doubly so.

The quartary

The notion of 'row level locking' is obsolete. It's nearly impossible to test and requires either highly error-prone and finicky figuring out which rows to lock, or a very broad locking strategy which means the app doesn't scale.

The proper way to do this is with optimistic locking, and the SERIALIZABLE transaction level. Any transaction level less than SERIALIZABLE has all sorts of real nasty caveats. For example, if this is the pseudocode that runs your ATM machines:

AccountId accountId = cardReader.getAccountIdFromInsertedCard();
int requested = askUserToPickAmount();
db.startTransaction();
int balance = db.select("SELECT balance FROM account WHERE accountId = ?", accountId).singleInt();
if (balance < requested) throw new InsufficientBalanceException();
int newBalance = balance - requested;
db.update("UPDATE account SET balance = ? WHERE accountId = ?", newBalance, accountId);
db.commit();
cashEmitter.emit(requested);

Which seems fine, has transactions and all that, it is definitely broken on everything except SERIALIZABLE. If you make that SELECT .. FOR UPDATE it depends.

The one downside to serializable is that the DB will occasionally throw an SQLException whose state type code indicates (and these codes aren't unified, so you need to code a detector for this per DB engine) a 'retry exception'. The response to that is literally what it says: Start again. Except, because computers are reliable, it's going to end as if you are about to bump into someone on the street, except it's a mirror. You dodge left, they dodge left. You crash into each other. Solution: Introduce randomness. See the Ethernet protocol. Point is, it's really complicated to get this right, you do not want to handroll any of this.

Hence, never use direct JDBC, it's not meant to be consumed as an API for end products. Use JDBI or JOOQ instead; they have far nicer APIs and have this built in, mostly (the docs explain how to set this up right and get transactional, automatically re-trying lambdas and the like involved).

Answer 2

Based on the source code you shared, you're running Derby in the "embedded" configuration, but since you want to have multiple clients sharing the same database, you need to be running in the "client-server" configuration of Derby.

Here's some introductory docs on the different configurations: https://db.apache.org/derby/docs/10.15/getstart/cgsquck70629.html

With the client-server configuration, a Derby server process ("the Derby Network Server") provides access to the database, and Derby client processes run independently, either on the same physical machine or on multiple separate machines, connecting to the Derby Network Server using network connections.