Transactions ... or something
I posted this message in a more general-purpose forum, but then found this more appropriate forum. Sorry for the duplication!
I am creating a database that needs to lock users out of updating certain portions if another user is currently updating the same portion.
I currently have a "locked" column in table, which is set the the user i.d. of whichever user currently owns that lock. No other user is allowed to obtain that lock until the first user releases it.
I need to write a Java method that will obtain the lock if it is available, then return the name of the user that currently owns the lock. I could do something like:
"UPDATE lockTable set owner='User A' WHERE portion=12 and owner=null"
return"SELECT owner FROM lockTable WHERE portion = 12"
But this suffers from a problem: another user could unlock portion 12 between running the two queries, and the method would return null.
Can anyone help me?
Thanks you!
[1065 byte] By [
Xemnosysta] at [2007-11-26 22:11:42]
# 1
> I am creating a database that needs to lock users out
> of updating certain portions if another user is
> currently updating the same portion.
Why would you ever want users to be able to update simultaneously? Could you describe your actual problem, rather than your proposed solution, please? It sounds like the answer to your question may be "transactions" without any need for this additional "lock" table.
# 2
> Why would you ever want users to be able to update
> simultaneously? Could you describe your actual
> problem, rather than your proposed solution, please?
> It sounds like the answer to your question may be
> "transactions" without any need for this additional
> "lock" table.
I don't want them to update simultaneously, that's the point. In fact, two users should not be able to have data open for the same department (formerly called "portion") at the same time. The application reads in a bunch of data about a department, then the user can edit those data. When they are done, they can save it all back to the database. SO, during the time that User B is viewing/editing a department, User A should be locked out. My question above is in regards to the situation when User A is trying to obtain the lock at about the same time User B releases it (causing the race condition I mentioned).
Thanks!
# 3
Ooo, it looks like this might be the answer? Where can I find out more about the behavior of the TRANSACTION_SERIALIZABLE mode?
conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE)
conn.setAutoCommit(false)
stmt.runUpdate("UPDATE lockTable set owner='User A' WHERE portion=12 and owner=null")
ResultSet r = stmt.runQuery("SELECT owner FROM lockTable WHERE portion = 12")
conn.commit()
String owner = r.getString(1)
stmt.close()
return owner
Can anyone tell me whether or not this actually accomplishes what I'm looking for?
Thanks!
# 6
Yes, your serializable isolation level will probably achieve what you're after; SERIALIZABLE means that all database access is as if users were forced to take turns to access the database, so it becomes impossible to corrupt the data that someone else is using.
Note that this only applies to transactions at this isolation level - it's entirely possible for a transaction at a different isolation level to see incomplete transactions.
If you want more on the subject, google for ACID and database.
(edit) Sorry, "ignore him" was over harsh. Mchan has a good point, but it doesn't directly address the question you asked.
