Database Locking

[Toni Eibner]

Can someone please explain database locks to me? Is there anything I need to set on
the database itself when created or should locking occur without having to 'set' anything?
I've had reports of 2 users being able to open the same record at the same time and
each make modifications... Is this possible?? Any suggestions or thoughts?

I would appreciate any advice. Thanks!
Toni Eibner

[Craig]

On 12/30/98 6:58:16 AM, Toni Eibner wrote:
> Can someone please explain database locks to me? Is there anything I need
> to set on
the database itself when created or should locking occur without
> having to 'set' anything?
I've had reports of 2 users being
> able to open the same record at the same time and
each make
> modifications... Is this possible?? Any suggestions or thoughts?

I
> would appreciate any advice. Thanks!
Toni Eibner

+++++++++

There is no parameter to set to invoke locking, this is handled by the kernel. Two users could open the same record at the same time and update it, but they would get serialized at the server and be done one after the other.

The LE parms enable you to customize the BEHAVIOR of locking and when it occurs, but you do not need to worry about turning it on or off.

[Toni Eibner]

OK, I've witnessed this problem first-hand now. If user1 opens a record and makes a
change (but doesn't save it yet) ,user2 opens the same record and makes
a change,user1 saves the record and then user2 saves their record, all my changes
are overwritten by whatever user2 has done. Why is this happening??? Better
yet, what can be done to correct this?

Toni Eibner


> On 12/30/98 6:58:16 AM, Toni Eibner wrote:
> Can someone please explain
> database locks to me? Is there anything I need
> to set on the database
> itself when created or should locking occur without
> having to
> 'set' anything? I've had reports of 2 users being
> able to open the same record at the same time and
each make modifications...
> Is this possible?? Any suggestions or thoughts?

I
> would appreciate any advice. Thanks!
Toni Eibner

+++++++++

There is no parameter to
> set to invoke locking, this is handled by the kernel. Two users could open
> the same record at the same time and update it, but they would get
> serialized at the server and be done one after the other.

The LE parms
> enable you to customize the BEHAVIOR of locking and when it occurs, but you
> do not need to worry about turning it on or off.

[Nigel Rivett]

I think this is a front end thing.
From the servers point of view getting a record and updating it are not really connected as the update could easily be done without the initial read.
To serialise the updates a transaction needs to be created and locks held.

something like

begin transaction
select i from TableA (updlock) where id = 1
..........
update TableA set i = 5 where id = 1
commit transaction

Would serialise things but without the transaction the server won't restrict read access.

An easy way of preventing your problem without blocking users is to use a timestamp field on tables and to check the value before the update - if it is not the same as the value retrieved and passed back with the update then return an error (optimistic locking).


On 12/30/98 12:56:07 PM, Toni Eibner wrote:
> OK, I've witnessed this problem first-hand now. If user1 opens a
> record and makes a
change (but doesn't save it yet) ,user2 opens the
> same record and makes
a change,user1 saves the record and then user2
> saves their record, all my changes
are overwritten by whatever user2 has
> done. Why is this happening??? Better
yet, what can be done to correct
> this?

Toni Eibner


> On 12/30/98 6:58:16 AM, Toni Eibner wrote:
>
> Can someone please explain
> database locks to me? Is there anything I
> need
> to set on the database
> itself when created or should locking
> occur without
> having to
> 'set' anything? I've had
> reports of 2 users being
> able to open the same record at the same time
> and
each make modifications...
> Is this possible?? Any suggestions or
> thoughts?

I
> would appreciate any advice. Thanks!
Toni
> Eibner

+++++++++

There is no parameter to
> set to invoke locking,
> this is handled by the kernel. Two users could open
> the same record at
> the same time and update it, but they would get
> serialized at the
> server and be done one after the other.

The LE parms
> enable you to
> customize the BEHAVIOR of locking and when it occurs, but you
> do not
> need to worry about turning it on or off.

[Bryan Dunn]

On 12/30/98 12:56:07 PM, Toni Eibner wrote:
> OK, I've witnessed this problem first-hand now. If user1 opens a
> record and makes a
change (but doesn't save it yet) ,user2 opens the
> same record and makes
a change,user1 saves the record and then user2
> saves their record, all my changes
are overwritten by whatever user2 has
> done. Why is this happening??? Better
yet, what can be done to correct
> this?
> Toni Eibner

Toni,
This is because you're working with a result set rather than "sitting on"
the record the way you might in an xBase, Access, or other PC DBMS. This is
as it should be, it's the way SQL products are designed. Nigel's suggestion
of using a Timestamp column for concurrency checking is the way we handle
this in our applications. If you start doing a lot of explicit locking
you're sort of limiting SQL Server's abilities and performance by making it
act like a PC database. If you want to have some fun, start a discussion of
whether or not concurrency checking (by locking or otherwise) is even
necessary. That used to be good for lots of "discussions" on the Compu$erve
SQL Server forum. In many of our databases, "last one in wins" actually
works best and lets SQL Server do its stuff at full speed so we've started
implementing that instead. My front end developers like it also since it reduces their workload a good bit.
Bryan