I’m at a large company with a world-wide SQL database. I’ve been
asked to create an application which is going to start as a reporting
tool, but will eventually (nlt v 1.4), start writing data. As usual,
corporate IT is giving the standard “no way, Jose’” response. My
management is not impressed with them, but I’m wanting to confirm what
I can expect from rails in this environment.
-Rails allows (non-default) concurrent access. Does this only affect
rails, or are we talking about locking everyone without this? What is
the granularity of the lock? (db/table/item)
-As I read ActiveObject, it appears that once read, a line is held in
memory until it is forced to the db. Is this correct?
-The listing code shows re-running the query for every page view. In
my environment, it would seem better to pull down the table once. ???
Pointers are fine, I’m not opposed to reading. 
When you say locking data, that can mean several things. One is to
explicitly lock a table, row in a table or a page. That’s a function
of your database. The second is locking to provide ACID properties
around database updates. You can wrap calls to active record objects
around a transaction, just as you can with JDBC, ADO.net, etc.
Finally you have optimistic locking which uses some convention to
ensure the record hasn’t changed since the client last retrieved it.
(This is a common problem on the internet as I retrieve a record,
modify it on a web page, and then submit the changes. Since
transactions do not span multiple http requests, it’s quite possible
for someone to retrieve the same record while I’m modifying it and
modify behind the scenes). Active record uses a version_id field on
the table so that saving a record with an stale version_id causes an
exception.
So rails won’t lock out other apps, just throw exceptions
appropriately?
WITHIN ONE TRANSACTION:
Again, that’s a function of your database. Transaction locks will
span applications. For example, if you start a transaction in Rails
that accesses resources currently held by a transaction initiated in
an ADO.net process, the Rails transaction will wait on the lock until
the ADO.net app either rolls back the transaction or commits. The
converse is true, as well. This is handled by the database and is
not a function of the toolkit/language used to access the database.
ACROSS MULTIPLE TRANSACTIONS:
If you have a .NET app and a Java app accessing the same data over
multiple page requests, there’s no way for one to lock out the other
unless you do one of the following:
- Some sort of optimistic locking convention
- Lock the account for the .NET or Java login id.
- Lock the table/page/row being accessed by the .NET or Java
application
- Turn off the application.
In .NET, as well as Rails, optimistic locking is not enforced across
applications. The last version of J2EE I worked with did not have
optimistic locking at all. (I think .NET’s optimistic locking simply
added the original values of the row to the where clause so you
wouldn’t find the row to update if it had been changed since your
retrieved it.) I don’t believe there’s any real standard for
optimistic locking so different apis/toolkits do it any way they want.
If two rails apps that support optimistic locking, rails style, try
to update the same record at the same time, one will fail. A .NET
and a Java app will merrily clobber each others’ data under the same
scenario. As would a rails app and a Java app. Essentially, the
ability to lock out other apps is not something anyone else provides
in a standard fashion, either.
EXPLICIT LOCKS:
If you absolutely have to lock a table/page/row, your database will
have a table/page/row locking command that will prevent anyone else
from accessing that data while you hold the lock. However, having to
hold explicit locks like this is usually a special case and generally
is not recommended. The last time I used a similar feature was to
lock a table that was refreshed on a nightly batch. The only reason
it was locked was to prevent access while the entire table was
truncated and the batch process fired off Oracle’s SQL Loader to
reload fresh data.
