>Vin,
>SNIP
>>> SNIP <<<
>>
>>Unless your app has a very small number of users, who are unlikely to be accessing the app at the same time, it would be a very bad idea to lock a page while the user is updating information on a screen (the old-fashioned Pessimistic locking).
>>
>>This is bad for a client-server app, because it will lock users out of otherwise-unrelated rows in the table. It can be catastrophic for an n-tier app working under COM+ with an STA model (or even MTA, but especially STA) because in addition to locking users out of rows, you will hold ADO connections, lock threads, and thereby affect even those processes that do not deal directly with data.
>
>I'm not so sure about that...I quote the following from "Inside Microsoft SQL Server 7.0" pp440 (bolding is mine): Every type of data modification performed in SQL Server requires some form of exclusive lock. For most data modification operations, SQL Server considers row locking as the default, but if many locks are required, this could escalate to page locks or even a full table lock.".
>So I think your premise about PAGE locking happening, and locking out users from other records unnecessarily, is not correct. Especially in the case that the application 'needs' a pessimistic strategy for safety purposes, because in that case the likelihood of "many locks" is near zero.
>

I guess we would need to know more about the exact database uses to determine whether it would escalate. I tend to assume that it will, to be safe. But, your point is well taken.

Nonetheless, holding a connection to even a single row for an extended period of time is not a good idea in a heavy-user and/or multi-tier environment. But, the original post didn't specify that so I am not sure it applies -- it's worth stating and knowing, though, for sure.

>>
>>I don't think Jim short-changed him at all. In fact, I would have been more emphatic. I'd argue that designing a c/s app with pessimistic locks is akin to designing, say, a VFP app that will store its data in notepad files rather than dbf's.... it just doesn't make sense, and even if you can do it, you either need to step back and re-think what you are trying to accomplish, or ask yourself if you are working with the right tool for the particular job.
>
>Well the question was how to do it, not if it should be done or not. The originator has a need, and he was told it couldn't be done. Based on other replies, that answer sure looks wrong to me.

Okay, I follow you. But I don't agree -- instead, I think that answering "it's really not in your interest to do this, and here's why" is appropriate in a forum like this, especially as so many folks struggle to move to c/s or multi-tier apps.

But yes, there are ways to do this. You're right.

>And what's this 'right tool for the particular job' business???? Since when does a need for a pessimistic locking approach disqualify a tool???
>

Wow. Seven question marks. I will try to provide a suitable answer! <s> That statement was one side of a two-sided argument I presented, wasn't it? I said that if one is trying to use a tool in a way that is waaaaaay out of the norm, he/she should step back and consider what he or she is trying to accomplish, or whether they're using the right tool for the job.

If there is a real business need for a database level row lock, there are all sorts of designs and languages that are not appropriate. I stand by that assertion.

>I read Jim's argument and chose not to respond. This message kind of forced the issue.
>
>JimN
>
>>
>>Vin