Is there any solution to perform the same/similar database operations simultaneously via multiple threads?

Java Q&A brought by php editor Zimo: Is there a solution to enable multiple threads to perform the same/similar database operations at the same time? During the development process, the issue of how to effectively handle multiple threads operating the database simultaneously has always attracted much attention. This article will introduce several solutions, including the use of database connection pools, locking mechanisms, transaction processing and other methods, to help developers solve this problem. Let's explore how to implement multi-threaded concurrent database operations to improve system performance and stability.

Question content

Using Java, Spring Boot and Hibernate, is there a way to allow multiple threads to perform a given operation simultaneously without causing deadlocks or stale data exceptions.

For example, suppose the automated tool logs in twice at the same time, which results in one of the following situations:

1. Deadlock because two threads (requests) update user records at the same time, e.g. storing the date/time of the user's last login.
2. Stale data exception because one thread updated the user before another thread's transaction completed.

This could be solved by adding synchronization on the user ID, but such a thing would have to be done everywhere , resulting in a potential performance loss and making the codebase more bloated and difficult to maintain attention.

Alternatively, we can adjust the isolation level (maybe we can set the default level globally). Is this a better solution or are there other solutions?

Solution

Pessimistic locking.

Lock all rows that may need to be updated early in the transaction. You can do this using locking read.

select ... from users where user_id = ? for update;

Only one thread will get it. Other threads will wait for this thread to release the lock.

Meanwhile, the thread holding the lock can update the row when ready.

update users set last_login = now() where user_id = ?;

Then, to be polite to other threads, please submit as soon as possible after update. This automatically releases the lock.

COMMIT;

This allows the next queued thread to acquire the lock it is waiting for.

You can use locking reads to lock multiple rows, even multiple rows in multiple tables. select Any rows checked by the query will be locked. If you need to lock rows in multiple tables, you can perform operations such as join.

Lock acquisition is atomic, so a given lock statement must acquire all requested locks, otherwise it must wait.

The above is the detailed content of Is there any solution to perform the same/similar database operations simultaneously via multiple threads?. For more information, please follow other related articles on the PHP Chinese website!