How to lock recordset in a multi-user environment 2

[sonper]

Hi fellas! Does anyone here have an idea of how am I going to lock a recordset (one record) in a multi-user environment? I'm trying to create a login module wherein once a user has successfully logged-in on one workstation using a UserID, he can no longer use the same UserID in another workstation. Also, when a record in open on a certain workstation, it should not be accessible to another workstation.

I'm using VB6 and MS Access.

Please help!

 

[mmilan]

I wouldn't use recordset locking for that, but that's just me.

Why not implement a boolean field on your users table to show whether the person is currently logged in, and prevent subsequent login attempts if they are?

If you go this way though, remember to set the value to false when the user logs out or the program ends.

 

[petermeachem]

There is a potential problem here. If the programme is stopped rather suddenly, such as by pulling the mains plug out, you won't have logged out, therefore you can't log in.

I don't actually have a solution for this. Just thought you would like to know the problem. Peter Meachem
peter@accuflight.com

Support Joanna's Bikeathon

http://www.accuflight.com/blue_peter_bikeathon_2002.htm

 

[mmilan]

Good point. I cottoned onto that as well whilst having dinner with my Mum, but not being near a internet connection I couldn't post...

Let's see... One solution, though devilishly complicated, would be to proceed as I outlined earlier, but have the second connection check that the first computer is actually running the program (Winsock control + events). You'd need to add the name of the current PC to the users table as well though, so the computer attempting a second connection would know where to send the network message.

I don't like it though, it seems overly complex for what we are trying to achieve. Let me sleep on it...

mmilan

 

[sonper]

I've actually done what mmilan has just outlined but the problem is (like what petermeachem had mentioned) when the application was not properly closed or shutdown, the value would remain as though I've already logged-in.

I'm really not that well-versed when it comes to Winsock control, can you please give me the details of what you're trying to point out by giving examples if it's ok with you?

Thanks.

 

[mmilan]

I haven't got time at the moment to provide actual examples of the use of Winsock ( 12.43am now, I'm on shift at 9am), but I can give you a quick overview...

The winsock control basically allows you to communicate over a LAN using the network cards - avoiding all the tedious business of setting up files etc... It can send messages (essentially strings of your choosing) out to a another PC, and it can also receive them. When it receives a message, an event is triggered and the message itself comes in as a parameter - I *THINK*

When you send a message(using a method on the control) you have to send it not only to the correct computer (computer name, which is why I suggested adding it to the user table), but also to the correct Port Number - which in simple terms is a funnel that the other machine should be listening to.

However, I'm not going to say much more in this post, because I really can't believe that Winsock is the way to go. There has to be a better solution, so I'd have a look at some of the VB sites on the web, as this is bound to be a common problem.

mmilan

 

[mmilan]

I think I've found an example of Winsock you might want to look at. Have a look at

http://www.15seconds.com/issue/010820.htm

Only had the briefest look at the page, but it looked promising...

mmilan

 

[sonper]

Dear mmilan

Thank you for spending time answering my queries. I really appreciate your effort of finding solutions to my problem. I've seen the example and I find it very informative and useful but I agree with you that Winsock is not the way to go. I believe that there's a better and not so complex solution to this. Sorry for not being aware of the time (it's 9:00 am here). It's really an urgent need. Please if you have found something, let me know. Thanks again.

 

[recurse]

sonper,

Yes, you can use record locking to implement the behavior that you are describing, but locking techniques are wildly different dependg on the backend you are using. If you are using a SQL Server backend you can use a WITH locking hint on your sql query which will lock the records at the specified granularity (PAGE, TABLE). This will cause all queries against that table or page to block until the lock is released. Then you would use the LOCK_TIMEOUT property on the connection to query the table, set it to 0 and a trappable error results from attempting to query on a locked resource. You will need to make sure you get an EXCLUSIVE lock.

 

[mmilan]

Wouldn't that still leave the problem though of if the program was terminated using cntr-alt-del or whatever, you would not be able to log in again?

Although the connection object would no longer be running, the server wouldn't know about that, and so the lock would remain in place. Or am I (probably) missing something?

 

[GlynA]

mmilan,

1.I have not used SQL Server but record locks are usually automatically released when a connection is terminated so if you decide to use record locking then abnormal termination is not a problem.

2.If you store the logged on status in a file then it is a good idea to store the terminal in use. If a user attempts to log on then you permit this if the terminal is the same as that stored in the file. This gets round the problem of a user being locked out after abnormal termination.

 

[mmilan]

Point 2 is pure inspiration - I've even starred you for it. Should have spotted that one myself. If the code does terminate abnormally, all you need do is have the user log on from the machine where the crash occurred, and you're back in buisness. Lovely.


Point 1 may, or may not, be suspect though. Issuing Cntrl_alt_del does not allow the software to run any garbage collection procedures, but instead merely kills the process. That means that the server would be unaware of the client's unfortunate accident, doesn't it?

I could be wrong there, because I'm not an expert on connections, but I think you need to be ABSOLUTELY sure that the record lock would be released before embarking on that route. Of course, you could always knock up a demo app to test it out...

mmilan

 

[sonper]

GlynA & mmilan,

I'll be honest with you guys! I can't relate to what you were discussing. I'm not that familiar with SQL SERVER. As I mentioned earlier, I use MS ACCESS as my backend tool. It's really an urgent need and I really don't think I can handle a more complex thing like SQL SERVER.

To mmilan,
What do you mean when you say "you allow the user log on from the machine where the crash occurred?" Could you please tell me exactly how to do it?

Please guys, it's really an urgent need.

 

[mmilan]

Basically, what you are trying to do is prevent a user from logging on at two machines simultaneously. This would be easy enough to do with a boolean field in your users table to show whether a user was logged on or not. However, the problem is that say Fred's using your software, and his machine dies for whatever reason, he will still be shown as logged on in the users table...

So, when Fred restarts his machine and tried to log back into your software, the software would see the "currently logged on field" was set to True, and wouldn't let him in.

What GlynA is saying is that while generally speaking you would not want to let the user log in if the flag showed he already was logged in, you should make an exception if the user is trying to log back in from the machine that is shown as logged in on the users table.

That means that you are going to have to store the name of the computer (or some other identifying feature) into the users table. You can get at the name of the PC through the Win32 API.

Martin.

 

[petermeachem]

I use the machine name here and there. The problem is that there is a lot of code required to do this. Perhaps you should start to ask yourself whether you actually need this feature. You may well end up with a complicated pile of code that will be more trouble than if you let people log in on more than one pc. Peter Meachem
peter@accuflight.com

Support Joanna's Bikeathon

http://www.accuflight.com/blue_peter_bikeathon_2002.htm

 

[Fursten]

recurse,

Can you give me a sql example of using "WITH locking" or somewhere I can read about it?

I´m using ADO and, in theory, recordset locks should resolve this kind of problems (to be able to lock a record)...however it doesn´t work. Microsoft and everywhere I read about locking says that or we use "with statements" or recordset locks, not the two of us at the same time, but I think that "with statement" is the only way to lock a record.

For instance, imagine there are 2 computers using the same application and the same database. Computer A makes a select and gets two records and it will edit the first one.
While he is editing(and making calculations that probably will change the record), the second computer makes the same select, getting the same records...(the first record being editing wasn´t updated yet! Even that I choose pessimistic locking in the recordset, computer2 will always be able to get and edit the first record...because computer1 didn´t lock the record he was editing (that will or not be in fact updated...) Is true that computer1 may never change the record he is editing, however if he change it, computer2 will be making calculations with a value that as changed... so the calculations will be wrong.

I think Ado as a "record" object, not the "recordser" object that it should able us to do such lock... however I never use it...

Any ideias?

Sérgio Oliveira

 

[mmilan]

Peter - isn't there a class somewhere for getting such information about the PC? If not, there should be!

 

[sonper]

mmilan,

I think I got a picture now of what you and GlynA are trying to say. I have to add 2 fields in my user table, 1 is to check for the status if a user has logged in and 1 to store the computer name in it. Let me just illustrate:

Say for example, Computer A has logged-in, my user table will have the following data:

USERID LOGSTAT COMPUTERNAME

U-001 TRUE COMP A

When COMP A suffers an abnormal shut down, user U-001 will still be allowed to log-in co'z he's just logging back on COMP A. What if user U-001 logged-in on a different terminal say COMP B, will he be allowed? I'm asking this because our current setup here is to allow any user to log-in on more than 1 pc. Any comment or suggestion you can give? Or am I missing something here?

Thanks again.

 

[GlynA]

sonper,

I think you have the right idea. U-001 will not be allowed to log on using a different terminal such as COMP-B.

You can simplify things by not having the LOGSTAT field. Make sure the COMPUTERNAME field is cleared when the user logs off and then allow the user to log on if the COMPUTERNAME is either blank or equal to the current terminal.

One more thing to be careful about is that if you check the contents of the database and then update it afterwards there is always the possibility of someone else logging on at the same time as follows:
1. User A checks database - it is free
2. User B checks database - it is still free
3. User A updates the database
4. User B updates the database

If this is a problem you can get round it by using SQL to retrieve and update the record at the same time.

 

[petermeachem]

Martin, I've not seen any, mind you I haven't looked either.

I don't wish to be awkward, but what happens if Comp A burns down. Poor old U-001 will never be able to log on again. You are going to have to have an admin screen, and then you have to worry about who is allowed to make changes on it, and make sure they are around when Comp A bursts into flames. I've been through something like this before. It just got more and more complicated and I eventually persuaded the customer it was a bad idea. All users had a login and personal password, which seemed to do well enough.

Peter Meachem
peter@accuflight.com

Support Joanna's Bikeathon

http://www.accuflight.com/blue_peter_bikeathon_2002.htm