1

I have the following scenario. I have two tables. One stores multi values that are counters for transactions. Through a java application the first table value is read, incremented and written to the second table, as well as the new value being written back to the first table. Obviously there is potential for this to go wrong as it's a multiple user system. My solution, in Java, to the issue is to provide Locks that have to, well should, be aquired before any action can be taken on either table. These Locks, ReentrantLocks, are static and there is one for each column in Table 1 as the values are completely independent of each other.

Is this a recommended approached?

Cheers.

Improve this question
0

2 Answers 2

Reset to default
5

No. Use implicit Database Locks1 for Database Concurrency. Relational databases support Transactions which are a vital part of ACID: use them.

Java-centric locks will not work cross-VM and as such will not help in multi-User/Server environments.

1 Databases are smart enough to acquire/release locks to ensure Consistency and Isolation and may even use "lock free" implementations such as MVCC. There are rare occasions when explicit database locks must be requested, but this is an advanced use-case.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Sorry, I wasn't very clear. The application will be running on a single JVM which users will be logging into. If I am reading you correctly you are saying that it isn't needed and that the DB will do all he heavy lifting here
@Medu It may be needed for other reasons; however, I am a firm believer in that the database - a proper RDBMS - and access to it should be it's own guardian. There is also often an issue of sharing connections between threads: easiest is to not do it :)
Thanks for the input everyone, I believe you have steered me in the right direction. I was already using transaction, I just didn't realise that the DB would take care of the locking without any input hence the java 'hack'.
1

Whilst agreeing with some of the sentiments of @pst's answer, I would say this depends slightly.

If the sequence of events is, and probably always will be, essentially "SQL oriented", then you may as well do the locking at the database level (and indeed, probably implicitly via the use of transactions).

However if there is, or you are planning to build in, significant data manipulation logic within your app tier (either generally or in the case of this specific operation), then locking at the app level may be more appropriate. (In reality, you will probably still run your SQL in transactions so that you're actually locking at both levels.)

I don't think the issue of multiple VMs is necessarily a compelling issue on its own for relying on DB-level locking. If you have multiple server apps accessing the database, you will in any case want to establish a well-defined protocol for which data is accessed concurrently under what circumstances. And in a system of moderate complexity, you will in any case want to build in a system of running periodic sanity checks on the data. (Even if your server apps are perfectly behaved 100% of the time, will back end tech support never ever ever have to run some miscellaneous SQL on the database outside your app...?)

Improve this answer

1 Comment

It's not "locking" per-se at the DB level (locks are one method but there is also MVCC and such) that is the key; rather using the DB properly guarantees ACID across clients/connections. I up-voted for some points raised, although I disagree about the conclusion - database level transactions support heavy-weight code DALs (although there are alternatives to "large transactions" like optimistic concurrency, which still require transactions) - it's not quite the same debate as SProcs vs ORMS.

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.