General discussion

Locked

SQL - Mutliuser Concurrency

By Limbo ·
Good day all,

OK here's the question...

Q: Which is the best programatic method for placing a database into a read only mode, and then freeing it as a user logs in then out?


Our development team have a particular problem at the moment which requires some lateral thought to affect a solution. We are currently implementing the design for a n-Tier application, written in VB6 with the proprietry database first being realised in SQL Server 7 (Orcale and SQL Server 2000 solutions are being designed in parallel).

What we require is your opinions of the most effective way of allowing users to 'Check-In' and 'Check-Out' an entire database.

Some points to guide you:

1. There is more than one database
2. Each database is identical in structure
3. The FIRST user to login MUST get full Read/Write access
4. Other users will then only get Read access


All answers will of course be warmly received, and given full consideration.

Tank you.


George

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

SQL - Mutliuser Concurrency

by LexingtonIT In reply to SQL - Mutliuser Concurren ...

Since you are using N-Tier structure. The simple resolution would be designing different connection BOs for readonly and read/write. Use an SharedProperty to determine whether it is the first user login or not, and also when the first user logout, you need set the property so that future user would acquire full write-read access.

HTH

Willie

Collapse -

SQL - Mutliuser Concurrency

by Limbo In reply to SQL - Mutliuser Concurren ...

Thank you Willie,

Your assistance is greatly appreciated.

It would seem prudent to create separate BOs to provide read only and read/ write data access. The SharedProperty you describe seems to be the key issue here, and I would like to hear some suggestions on how it would be best implemented.

Which reliable method would you use to determine the number of logins in a SQL Server environment?

What about orphaned sessions? Wouldn't they produce a massive problem?

Collapse -

SQL - Mutliuser Concurrency

by LexingtonIT In reply to SQL - Mutliuser Concurren ...

The reason to use sharedproperty is to make sure that all the sessions , though only one at one specific time, could access that connection object for read/write, set a variable to indicate whether it is active or deactivated, change it in the OBJECT_ACTIVATE, OBJECT_DEACTIVATE route.
Everytime, user request the connection, check that read/write connection object status, if this object is active, then not use it, if it is not active, use it. This way, even the first acquired W/R privilege user for some reason hasn't use that connection for a long time, other user could still acquire that W/R connection. If you implement like above, the following senario would occur: user A gets W/R connection, uses it and then deactivate it, then user Bacquires the w/r connection, uses it and releases it, then user A again could acquire the W/R connection when next time he request the connection object, and so on. I don't know whether this is what you want to achieve. But it seems to be the way to solve the dead connection or idle connection problem. I don't have sample code for sharedproperty, but the help file for MTS has a very simple example to show how to do it.

Collapse -

SQL - Mutliuser Concurrency

by Limbo In reply to SQL - Mutliuser Concurren ...

Nice one :)

I'll check up on the MTS example...

Have a good weekend.

Collapse -

SQL - Mutliuser Concurrency

by Limbo In reply to SQL - Mutliuser Concurren ...

This question was closed by the author

Back to Web Development Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums