-1

I'm working on a web application using Spring (Java and JPA + Hibernate) and I was wondering if there is a way of locking a MYSQL table and then when another web service (or even another thread from the same application) tries to read or write the locked table it waits for it to be unlocked.

For example, imagine a use case (transaction) that needs to read a value and then increment it. There is a concurrency problem, because 2 or more threads at the same time are trying to read+write the same table.

Do I have to program it manually? Is that possible with java or using any of his libraries?

Improve this question
5
  • 1
    dev.mysql.com/doc/refman/8.0/en/innodb-locking.html
    – devnull
    Jul 8, 2021 at 11:28
  • 2
    "or example, imagine a use case (transaction) that needs to read a value and then increment it." This is the wrong way to use an ACID database. If you want to update a value, use UPDATE table SET x = x + 1 WHERE x = old_value - if the value has been updated by another transaction, your transaction will fail and you can retry. Don't reinvent the wheel.
    – Philip Kendall
    Jul 8, 2021 at 11:36
  • 1
    do not lock tables if ever possible because this can, well, lock up your system. To increment a value you can read and increment the value within one SQL statement. If you want to use multiple statements you can check that the old value still holds (update table set field = new_value where field = old_value) or use select for update.
    – amon
    Jul 8, 2021 at 11:36
  • 3
    Do not lock tables. Use transactions, which exist specifically for this problem. A transaction effectively locks only those row in a table that are affected by your operation.
    – Kilian Foth
    Jul 8, 2021 at 12:21
  • 1
    The one and only time in my career I locked an entire table was to add a primary key to a table that had none. I won't go into why that table had no identifying column, but I locked the table because I added the primary key column as a nullable column, then populated it with data from a sequence in Oracle, then applied the primary key constraint. Outside of major table alterations like that, I would definitely not lock entire tables.
    – Greg Burghardt
    Jul 8, 2021 at 12:47

1 Answer 1

Reset to default
1

Others have commented on this - I'll state it bluntly.

Don't lock whole tables.

Web Applications are, by definition, multi-user, multi-threaded Beasts and each of those Users and each of those Threads will want to work with many rows in many tables all at "the same time".

Think of cars travelling on a motorway.

  • Closing a single lane slows things down a bit.
    Traffic in the affected lane waits, other traffic continues to flow.
  • Closing the whole motorway causes chaos.
    Everything stops. Huge queues form that take ages to clear, even once the motorway is re-opened.

The same thing happens in a database.

  • Using a Transaction slows some traffic down a bit (transactions should, of course, written to be temporally short).
  • Locking a whole table "logjams" anything and everything that wants to use that table. If that's a "core" table in your application? Big Trouble.

Learn about Transactions and how to read data reliably within those Transactions.

And don't lock whole tables ever again.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.