There is a system that allows users to work with files in the browser. The stack is standard: Java, Spring, React, Postgres. Files are stored in the file system on the backend, file metadata in the database. The team implemented a feature: @Transactional public void process(String oldName, String newName) { Long id = exec("select id from file where name='" + oldName + "'"); // executing a database query processFile(oldName, newName); // renaming the file on disk exec("update file set name='" + newName + "' where id = " + id); // executing a database query }

The provided code has several issues related to transactional integrity and data consistency between the database and the file system:

1. The @Transactional annotation ensures atomicity only for database operations, not for file system operations. If the file renaming (processFile) fails, database changes won't be rolled back, and vice versa — if the database update fails, the file will already be renamed.

2. Using string concatenation for SQL queries poses a risk of SQL injection. It's better to use parameterized queries.

3. The logic mixes database and file system operations in one method, complicating error handling and rollback.

Recommendations:

• Separate operations: first check the possibility of renaming the file, then perform the renaming, and only after successful renaming update the database.
• To ensure consistency, consider using a two-phase commit pattern or compensating transactions, as the file system does not support transactions.
• Wrap the file renaming in a separate service with error handling.
• Use parameterized queries for security.

Example of an improved approach:

@Transactional
public void process(String oldName, String newName) {
    Long id = jdbcTemplate.queryForObject(
        "select id from file where name = ?", Long.class, oldName);

    try {
        fileService.renameFile(oldName, newName); // renaming the file on disk
    } catch (IOException e) {
        throw new RuntimeException("Failed to rename file", e);
    }

    jdbcTemplate.update("update file set name = ? where id = ?", newName, id);
}

If the file renaming fails, the transaction won't proceed to update the database. But if the database update fails after renaming the file, you will need to implement compensation logic (e.g., revert the filename back).

For a more reliable system, consider storing files in specialized storage systems that support transactions or using a queue to synchronize changes.