Forcing a deadlock with MySQL transactions-CodePudding

I'm trying to force a deadlock for a demonstration and with Oracle, it seems to be working, but in MySQL, for some reason, the query goes through...

Why is my example not causing a deadlock??

@EventListener(ApplicationReadyEvent.class)
public void executeStartupTask() throws Exception {
    LOGGER.info("Connecting to MySQL via: "   databaseUrl);
    LOGGER.info("Seeding database...");
    seedDatabase();

    Thread providerResetThread = new Thread(() -> {
        try {
            Connection connection = getMySQLDataSource().getConnection();
            connection.setAutoCommit(false);

            // 1st device model always GMETER
            updateDeviceModel("E000000001", "GMETER", connection);

            LOGGER.info("Sleep for "   genericTimeoutLength   "  milliseconds");
            Thread.sleep(Integer.parseInt(genericTimeoutLength));

            // 2st device model always EMETER
            updateDeviceModel("E000000002", "EMETER",  connection);

            connection.commit();
        }
        catch(Exception e) {
            e.printStackTrace();
        }
    });

    providerResetThread.start();

    // Resetting the model type
    try {
        Connection connection = getMySQLDataSource().getConnection();
        connection.setAutoCommit(false);

        Statement statement = connection.createStatement();

        updateDeviceModel("E000000002", "NONE", connection);
        updateDeviceModel("E000000001", "NONE", connection);

        connection.commit();
    }
    catch(Exception e) {
        e.printStackTrace();
    }

    providerResetThread.join();
    LOGGER.info("EXECUTION SUCCESSFUL!");
}


public void updateDeviceModel(String deviceId, String model, Connection connection) throws SQLException {
    LOGGER.info("Updating model for device "   deviceId);
    PreparedStatement statement = connection.prepareStatement("UPDATE device_entity SET model = '"   model   "' WHERE deviceId = '"   deviceId   "'");
    statement.execute();
}

The log goes as following:

2022-02-08 09:59:32,950 INFO  method: [Thread-2] interview.InterviewApplication (InterviewApplication.java:126) - Updating model for device E000000001
2022-02-08 09:59:32,950 INFO  method: [main] interview.InterviewApplication (InterviewApplication.java:126) - Updating model for device E000000002
2022-02-08 09:59:32,955 INFO  method: [Thread-2] interview.InterviewApplication (InterviewApplication.java:56) - Sleep for 5000  milliseconds
2022-02-08 09:59:37,965 INFO  method: [Thread-2] interview.InterviewApplication (InterviewApplication.java:126) - Updating model for device E000000002
2022-02-08 09:59:37,975 INFO  method: [main] interview.InterviewApplication (InterviewApplication.java:126) - Updating model for device E000000001
2022-02-08 09:59:43,046 INFO  method: [main] interview.InterviewApplication (InterviewApplication.java:92) - EXECUTION SUCCESSFUL!

CodePudding user response：

Using your code in the repository that you indicted, I have the expected behaviour as you can see in the following image:

I've used MySQL 5.6 (also in the latest version the same occurs).

Also the code in the repository is not the same you posted here. When using the code you posted here the behaviour does not occur.

If you observe closely:

updateDeviceModel("E000000001", "GMETER", connection);

updateDeviceModel("E000000001", "NONE", connection);

Is using Ids that do not exist in database.

Your seed database uses IDs "E1" and "E2". In practice you are updating nothing.

Changing your execute statement to

final int update = statement.executeUpdate();

You can see how many records were affected (zero).

So no deadlock occurs.

In your repository code, if you do the same, you will see that one record was updated in each call.