File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Table-level locking, how? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Table-level locking, how?" Watch "Table-level locking, how?" New topic

Table-level locking, how?

Joe Vahabzadeh
Ranch Hand

Joined: Jan 05, 2005
Posts: 140

I suppose this is more a general database question than anything, but since I'm doing the code in Java, I figured I might as well ask here.

Is it possible to set and/or release a table-level lock without actually performing any sort of SELECT, UPDATE, etc?

What I need to do, in sequence of events, is:

1) Allow the user who is going to make changes to SELECT what they need, and lock the table when this SELECT occurs so that absolutely no one else can read the table after this select has occurred.

2) When this user is done, release the lock on the table when:

a) The user UPDATES the items in question . . or . .
b) When the user cancel's the operation. No updates are made, but I still need to release the lock.

Is what I'm looking to do possible? If so, how's it done?

I don't really know anything about locking when dealing with databases, but I know for the purposes of what I need to do, that I have to totally lock out the table from other users completely while this is going on.

The database is Microsoft SQL Server 2000, and I'm using the JDBC driver that Microsoft provides for use with SQL Server 2000.

David Harkness
Ranch Hand

Joined: Aug 07, 2003
Posts: 1646
Do you absolutely require the ability to lock out everybody? Specifically, if a developer or DBA logs into the database using isql (the interactive SQL tool that comes with SQL Server), should they be locked out of the table entirely? Or, would it suffice to simply lock out other users of your application from operating on the table? Finally, how long will the operation last?

I don't know that you can lock out the selection of data from the table. Most locks block writing, but I'm not at all familiar with SQL Server. Also, what happens if the user locks the table and then goes to lunch or is interrupted for a few hours? Is it okay for the table to be locked so long?

If it is even possible, the lock will be tied to the Connection (99% sure). This means that that Connection cannot be used by anyone else, increasing your resource requirements.

If the requirements can be laxed a bit, I would propose creating a table to hold "lock records" and perform all the locking in the application. When someone is going to perform this operation, they create a "write-lock" record. When someone is going to select from it, they create a temporary "read-lock" record, which will fail if there is a "write-lock" record.

I highly recommend laying out exactly why this needs to be done and thinking about other ways to solve the problem. Why would you want to block someone from reading data that someone else is simply thinking about changing? You may be able to find other ways to solve the problem that won't have such a high impact on the system.

Anyway, that's my 2 cents.
Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 33098

Welcome to JavaRanch! This is absolutely the right forum to ask database questions, whether they are java specific or not.

I agree with David that you probably don't want to lock the table for long. If the update is coming within the code (programatically), you can use a transaction. Depending on the transaction settings, you can effectively prevent users from reading rows during the transaction. You still have to do some type of query though.

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
Joe Vahabzadeh
Ranch Hand

Joined: Jan 05, 2005
Posts: 140
As it turns out, further information I'm getting indicates that my original question might now be a moot point.... However, the idea for my original scenario was:

An administrator, at the end of the day, is performing this action to make any changes. The lockout was to prevent a second administrator, not knowing the first admin was on a terminal somewhere, from using this program from a different terminal and making changes to the same data.

But yeah, I guess there's the problem of what happens if the guy just walks away.

Anyway, like I was saying, it looks like it might be a moot point because now they're talking about only a single admin on site at any given time.

Though, now I have a slightly different problem... except in this case there's no potential for the out-to-lunch scenario.

This program will be used on multiple workstations.

Whenever a user (regular user, not the end of day admin) enters a bunch of data and clicks submit, an entry will be added to the main table. The procedure is as follows, AFTER the user clicks on submit:

1) Open Connection
2) Get highest-existing entryNum (a column, integer) . . basically GET max(entryNum) FROM . . .
3) Create a new entry, using the value that we got in step 2 incremented by one, as the value for the new entryNum
4) Close connection

Although unlikely, I guess it's possible for a second terminal to finish step 2 before the first terminal performs step 3, but after the first terminal performs step 2.

My ideal situation in this case is to have a lock (or something to that effect) such that two different users using this program cannot get the same result for max(entryNum).

(For those wondering, by the way, even though it's a simple incrementing integer, I need a separate column for entryNum because it's a value that will be used in another table as a crossreference).

I'd therefore need to lock others out while any given terminal is going through this procedure, but am not sure what's the right way to handle this.

In the table that's being added to, entryNum is assigned as a column that should have unique values (in the crossreferenced table though, it's not unique, but that's a different story).

Should I just try to catch an sqlException and look for text in the exception message that makes reference to violating the UNIQUE limitation for the column? Or should I do a lock, since it's happening so briefly?

If the latter, then how?
[ January 26, 2005: Message edited by: Joe Vahabzadeh ]
David Harkness
Ranch Hand

Joined: Aug 07, 2003
Posts: 1646
The first question that would make this a trivial problem is Are gaps allowed or must the sequence be contiguous. IOW, is 1, 2, 4, 5, 8 acceptable? If that's acceptable, simply create a sequence or use a sequence table if SQL Server doesn't have sequences to use for entryNum.

A sequence (or sequence table if you have to roll your own) provides a sequence of unique numbers, increasing by 1 or any other number you choose, in a separate transaction. Thus, if you get the next sequence but your insert fails, that sequence number you obtained is lost. With a sequence table you could try to provide rollback, but if you don't care about gaps it's not worth it.

SQL Server does have identity columns that provide this functionality built in to the column, and I even believe there are hooks to retrieve the value that was assigned in the last insert statement. I'd investigate that angle.

Also, if your insert "cannot fail" then you don't really have to worry about creating gaps. Gaps only occur if you get a number and then fail during the insert.

This involves no locking on your part and is very easy to implement.

Now, say you must ensure there are no gaps, then the easiest solution is the one you proposed:You could probably even combine the two queries by putting the select into the insert statement as a subselect. I'll warn you that this method is typically frowned upon for the obvious reasons: contention, having to retry, inelegant. However, it gets the job done, and if likelihood of contention is low, it'll work just fine.

Where this method doesn't work so well is when you aren't coding your own JDBC. If you're using an Object Relational Mapping tool, it may batch your object changes, creating a larger window for contention. But it doesn't sound like you're using such a tool, so you should be fine.
Joe Vahabzadeh
Ranch Hand

Joined: Jan 05, 2005
Posts: 140
Looks like I'm going to be going with the method of checking for a string in the exception message that mentiones a violation of the UNIQUE KEY constraint.

I actually tried to do the select within the insert. Query Analyzer is fine with it, but Microsoft's SQL Server 2000 Driver for JDBC will have no part of that, insisiting that anything in the values has to be a discrete item and not a query.

What's really annoying is that the connection gets closed when I get an exception... even a unique key exception. That's not too much of a problem when I'm doing an individual INSERT, but there's another point in the code where I do a mass-insert using a prepared statement, so if a UNIQUE KEY violation occurs, I've got to reopen the connection and recreate my prepared statement.

Not insurmountable, just plain annoying.

Anyway, thanks for the advice!

EDIT: well, that's interesting. When having a regular statement and doing an executeUpdate, if a UNIQUE KEY violation exception occurs, the connection gets closed. If doing things with a preparedStatement and the same exception occurs, the connection remains open. Strange....
[ January 27, 2005: Message edited by: Joe Vahabzadeh ]
David Harkness
Ranch Hand

Joined: Aug 07, 2003
Posts: 1646
Originally posted by Joe Vahabzadeh:
Looks like I'm going to be going with the method of checking for a string in the exception message that mentiones a violation of the UNIQUE KEY constraint.
That's what I do with all of my applications, and it works quite well.
When having a regular statement and doing an executeUpdate, if a UNIQUE KEY violation exception occurs, the connection gets closed.
That is the most bizarre thing I've ever heard. I guess it's okay since prepared statements work, but another option is to investigate using a third-party JDBC driver. DataDirect makes one for Oracle, but I don't know if they have one for SQL Server.
I agree. Here's the link:
subject: Table-level locking, how?
It's not a secret anymore!