For the first time I encountered such a task, to implement a multi-threaded application using a database but without high-level libraries like Hibernate. Only JDBC. The essence of the game: there is a Clan object that enters into battle with other clans and, depending on the result, the state of its treasury changes. Along with this, the Event object is stored in the database, which stores information about who fought with whom, how it ended, etc. First, I implemented a clan repository:
public class ClanRepository implements TestTaskRepository<Clan> {
private static Connection connection = ConnectionManager.open();
private static ResultSetConverter resultSetConverter = new ResultSetConverter();
private static Lock lock = new ReentrantLock();
@Override
public Clan findById(Long id) {
lock.lock();
try {
Clan clan = null;
try (PreparedStatement preparedStatement =
connection.prepareStatement("SELECT * FROM clans WHERE clan_id=?")) {
preparedStatement.setLong(1, id);
ResultSet resultSet = preparedStatement.executeQuery();
resultSet.next();
clan = resultSetConverter.getClanFromResultSet(resultSet);
} catch (SQLException e) {
e.printStackTrace();
}
return clan;
} finally {
lock.unlock();
}
}
@Override
public void update(Clan clan) {
lock.lock();
try (PreparedStatement preparedStatement =
connection.prepareStatement("UPDATE clans SET clan_name =?, clan_gold=? WHERE clan_id=?")) {
preparedStatement.setString(1, clan.getName());
preparedStatement.setInt(2, clan.getGold());
preparedStatement.setLong(3, clan.getId());
preparedStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
lock.unlock();
}
}
I set up a lock in the hope that this would change something and wrote a thread class in which the battle takes place:
@Override
public void run() {
Random random = new Random();
boolean result = random.nextBoolean();
Clan currentClan = clanRepository.findById(CurrentUser.getClan().getId());
Clan enemyClan = clanRepository.findById(choiceOfEnemy().getClan().getId());
if (result) {
currentClan.setGold(currentClan.getGold 100);
enemyClan.setGold(enemyClan.getGold - 100);
} else {
currentClan.setGold(currentClan.getGold - 100);
enemyClan.setGold(enemyClan.getGold 100);
}
clanRepository.update(currentClan);
clanRepository.update(enemyClan);
}
As long as there is only one thread, everything works fine. When a second thread is connected, the data in the database is not saved correctly. I decided that the point is that there is a time gap between receiving clans and saving them in a modified state. In this regard, I wrote the ActionServise class where I collected everything in one method and took the code of this method into lock(), unlock().
public void action() {
lock.lock();
try {
Random random = new Random();
boolean result = random.nextBoolean();
Clan currentClan = clanRepository.findById(CurrentUser.getClan().getId());
Clan enemyClan = clanRepository.findById(choiceOfEnemy().getClan().getId());
if (result) {
currentClan.setGold(currentClan.getGold 100);
enemyClan.setGold(enemyClan.getGold - 100);
} else {
currentClan.setGold(currentClan.getGold - 100);
enemyClan.setGold(enemyClan.getGold 100);
}
clanRepository.update(currentClan);
clanRepository.update(enemyClan);
} finally {
lock.unlock();
}
In the run() method, I left only the call to this action() method It still saves incorrect data.
Among other things, even if this solution worked, it represents a point at which all threads will queue up and there will be no parallel battles (please correct if this is not true)
Then I googled on the Internet that the problem is in the connection, in that it is one for all threads. I tried creating a connection for each thread and passing it along the chain to the repository itself. Did not help.
Considering the age of the technologies in question, I am sure that my problem has been solved a long time ago, Please tell me how this is done.
CodePudding user response：
What you are trying to achieve is to have 4 SQL statement execute without anything else happening to the database in between them:
- The SELECT to read the current clan's gold
- The SELECT to read the enemy clan's gold
- The UPDATE to (say) increase the current clan's gold
- The UPDATE to reduce the enemy clan's gold by the amount that the current clan's gold increased.
The correct way to do this is to execute all SQL statements as a single transaction; i.e. using a single connection, execute:
BEGIN
SELECT 1
SELECT 2
UPDATE 3
UPDATE 4
COMMIT
Assuming that you have chosen an appropriate isolation level for the JDBC connection, and turned off auto-commit, the database itself will lock database rows to prevent any other simultaneous transactions from interfering with the two CLANS table rows that you are operating on.
Instead, you are trying to use Java locks to prevent the interference. In theory, that would work ... provided you did the locking properly AND no other application is accessing that table.
In practice, you appears to have flaws in the way you are doing the locking. (For example, it is not clear what lock object your run() method is using.) Furthermore, nothing stops another application (or another instance instance of this one) from accessing the table.
Even if your locking was effective, the scheme you are using seems to be locking the entire repository rather than just the two rows affected by the ... interaction between the clans.
CodePudding user response：
I agree with @StephenC about letting the database deal with locking. I would add that you can also use a connection pool (DataSource) to deal with contention on the database access.
Your ClanRepository class would look like this:
public class ClanRepository implements TestTaskRepository<Clan> {
private ResultSetConverter resultSetConverter = new ResultSetConverter();
private final Connection connection;
public ClanRepository(Connection cnt) throws SQLException {
connection = cnt;
}
@Override
public Clan findById(Long id) {
Clan clan = null;
try (PreparedStatement preparedStatement =
connection.prepareStatement("SELECT * FROM clans WHERE clan_id=?")) {
preparedStatement.setLong(1, id);
ResultSet resultSet = preparedStatement.executeQuery();
resultSet.next();
clan = resultSetConverter.getClanFromResultSet(resultSet);
} catch (SQLException e) {
e.printStackTrace();
}
return clan;
}
@Override
public void update(Clan clan) {
try (PreparedStatement preparedStatement =
connection.prepareStatement("UPDATE clans SET clan_name =?, clan_gold=? WHERE clan_id=?")) {
preparedStatement.setString(1, clan.getName());
preparedStatement.setInt(2, clan.getGold());
preparedStatement.setLong(3, clan.getId());
preparedStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
And your action method like this:
public void action() {
try ( Connection connection = dataSource.getConnection()) {
ClanRepository clanRepository = new ClanRepository(connection);
Random random = new Random();
boolean result = random.nextBoolean();
Clan currentClan = clanRepository.findById(CurrentUser.getClan().getId());
Clan enemyClan = clanRepository.findById(choiceOfEnemy().getClan().getId());
if (result) {
currentClan.setGold(currentClan.getGold() 100);
enemyClan.setGold(enemyClan.getGold() - 100);
} else {
currentClan.setGold(currentClan.getGold() - 100);
enemyClan.setGold(enemyClan.getGold() 100);
}
clanRepository.update(currentClan);
clanRepository.update(enemyClan);
connection.commit();
} catch (SQLException ex) {
ex.printStackTrace();
}
}