Distrbuted data input system using table row locks

[zoltan_ie]

Has anyone developed a multi user system using database table row locks to manage the data that users are modifying? Say where someone has requested data in the system and the corresponding rows (in multiple tables) in the database are locked until the user saves the data back down, logs out, times out (30 min inactivity) or moves on to other data?

Does anyone know any of the problems with using a technique like this? Would it scale? How would you approach this problem with multi users?

Or is it a good technique? What are the advantages of it?

Any opinions, ideas, links, info or anything at all would be greatly appreciated.

Thanks

[Krzemo]

To scale well, a multi user application has to not use any locks or to keep them as narrow as possible.One fine approach is to develop some kind of message queue system to manage request/response (no locks at all). That will allow to make partially offline system too.

Particular solution depends on RDBMS system U have. In MsSQL U can do it manually or using MSMQ (or other ready but not cheap solutions like Biztalk etc).

Best regards,
Krzemo.

[zoltan_ie]

Krzemo I'm pretty much of the same opinion

Personally I believe a client/server architecture is the best appropriate where the server keeps record of who's got what and so no table row locks are needed.

But I've been asked the research to see if it’s really possible to do such a system another way like I have described as it might be easier to do. I see no problems with the client/server architecture as I understand how it would work. But the table row locking is new to me and I don’t know how it would scale or how easy it would be to manage a system like that. It’s the kind knowledge you would really acquire through experience.

[zoltan_ie]

I've been reading around and I seem to get the impression that Row, Page & Table locks are only held for the duration of a transaction.

The system I’m trying to investigate to see if it would work like so…

+ User logs in

+ User request some records to modify

+ These are selected from the database and the rows are lock (App connects to database, retrieves and locks rows, disconnects)

+ The user modifies this data, taking say 30mins

+ User click update on the GUI and the changed data is saved to the database and the locks release (App connects to database, saves the data down, unlocks the records, disconnects)


Is this even possible?????

[craigcullen]

I am trying to develop a multi user data entry system also.

The plan was to use pessimistic locking - that is, to lock at a row level in the select statement, then at at later stage in the update procedure to commit the transaction thus releasing the lock.

However I am running into difficulites almost straight away. My application connects to the DB for the select, then it disconnects. It reconnects for the update.

Problem is that as soon as the connection is dropped so is the lock.


Does anyone know if there is any way of persisting the lock after the DB connection is released ? and if so if there is any way of a new DB connection identifying the lock in order to release it at a later stage ?

Thanks