How do you prevent multiple users colliding on a single record?
How do you prevent multiple users colliding on a single record?
How do you prevent multiple users colliding on a single record?
How do you prevent multiple users colliding on a single record?
How do you prevent multiple users colliding on a single record?
How do you prevent multiple users colliding on a single record? How do you prevent multiple users colliding on a single record? How do you prevent multiple users colliding on a single record? How do you prevent multiple users colliding on a single record? How do you prevent multiple users colliding on a single record? How do you prevent multiple users colliding on a single record? How do you prevent multiple users colliding on a single record? How do you prevent multiple users colliding on a single record?
How do you prevent multiple users colliding on a single record? How do you prevent multiple users colliding on a single record?
How do you prevent multiple users colliding on a single record?
Go Back  Xtreme Visual Basic Talk > > > How do you prevent multiple users colliding on a single record?


Reply
 
Thread Tools Display Modes
  #1  
Old 09-26-2008, 02:50 AM
IndraG IndraG is offline
Junior Contributor
 
Join Date: Feb 2006
Posts: 204
Default How do you prevent multiple users colliding on a single record?


Hi, I am in a bit of a jam here. I have an order form, that is accessed by two or more users

in different computers. The form is written in VB2k5, and it is writing to an Access database

on two tables, tblOrder and tblDetOrder. The tblOrder fills in the main part of the Order,

such as Order Number, Customer, Sales, etc. While the tblDetOrder fills in the detail parts,

such as stock code, quantity, price, etc.

The tblOrder primary key is a Long integer, but not autonumber (to take away the problem of

skipped numbers if the operator cancells/deletes, but generated by the code by querying the

Max(Number). Both the entry to tblOrder and tblDetOrder is saved silmutaneously with a

transaction, when the operator hits the OK button. I already done a precaution in the code,

where the code will checked if the number is saved, if it is, and there is no rows of the

details shown in the datagridview, it automatically researches for the max(number).

However, I still gets errors from the operators that will not be there if the form is only

accessed by a single person. So, apparently collisions happen. Can anybody give a better

suggestion than what I have done?
Reply With Quote
  #2  
Old 09-26-2008, 06:39 AM
martonx martonx is offline
Centurion
 
Join Date: Jan 2007
Location: Szeged, Hungary
Posts: 129
Default

You should try to use autonumber the primary key. I think skip a number not so big problem, like collisions in your business process.
If you give primary key in code, they can collision easily.
Or other method is use different primary id-s from different users.
User1 make primary key for example 1xxxxx
User2 make primary key 2xxxxx
etc
every user can make more thousands of orders, withouth collisions.
Reply With Quote
  #3  
Old 09-26-2008, 10:56 AM
Roger-U Roger-U is offline
Centurion
 
Join Date: Apr 2008
Location: Surrey, UK
Posts: 112
Default

Personally i'd use a semaphore -some DB's provide them. But in Access etc have a table e.g. semaphores and fields sKey and iFlag, and set a row with sKey='Flag1' and iFlag=0

then update sempahores set iFlag=1 where iFlag=0 and sKey='Flag1' and check the Rows Affected count - if it is 1 then go ahead, if it is zero then wait a few milliseconds then try again (presumably the other user got here first). when you've finished the updates then update sempahores set iFlag=0 where sKey='Flag1'

maybe have a timeout of say 10 seconds in case the other program crashed!

i know Transactions "should" do this... but...
Reply With Quote
  #4  
Old 09-27-2008, 07:33 PM
IndraG IndraG is offline
Junior Contributor
 
Join Date: Feb 2006
Posts: 204
Default

Dear Roger,
Can you describe a bit slower (or longer), please? It seems interesting, but I can't really catch it. Sorry, I never do this before. Much appreciated.
Reply With Quote
  #5  
Old 09-29-2008, 09:44 AM
Roger-U Roger-U is offline
Centurion
 
Join Date: Apr 2008
Location: Surrey, UK
Posts: 112
Default

Table: semaphores
Fields:
sKey Text
iFlag Integer
Row sKey iFlag
Flag1 0


You don't say what you are using to connect to the database? DAO RDO ADO other?



before performing any updates

Dim SQL$, n%, k%

SQL = "Update sempahores set iFlag=1 where sKey='Flag1' and iFlag=0

do
Connection.Execute SQL,n 'assuming ADO syntax
If n=1 then Exit Do
Sleep 100
k = k+1
If k > 100 then you've been in this loop 10 seconds - something is wrong!!
loop


... now do your updates
and if the other user hits the above Do Loop they will get n=0 until you
have finished and executed the statement below

after all your updates are done

Connection.Execute "Update sempahores set iFlag=0 where sKey='Flag1'" ,n


Hope this helps a bit... ciao
Reply With Quote
  #6  
Old 10-02-2008, 12:03 AM
IndraG IndraG is offline
Junior Contributor
 
Join Date: Feb 2006
Posts: 204
Default

I am sorry, I have been out of the work for a few days. OK, Roger. I am using ADO.NET of VB2005. May I ask you three of clearifying questions?

1. semaphores is a table with two fields and only a SINGLE row that has values of sKey = 'Flag1" and iFlag = 0 (or 1 depending on the user accessing it), right?

2. The design will allow only a single user accessing the database at any one time? I figure your design is that the iFlag is set to 1 during anyone accessing the database and nobody can access the database when iFlag = 1. Correct? I assume it can be designed that it will be as such for accessing a single table rather than the whole database.

3. I assume Connection is the OleDbConnection. I am confused with:
Connection.Execute SQL, n syntax, I can't reproduce it, is this VB code (sorry, I am purely VB guy)? The normal method I use is to use OleDbCommand to execute SQL, which I can execute the SQL without a problem, what I don't understand is what is n? And what it is for?

Sorry to sound so dumb, but the way you execute is truly new to me. Thank you for being so patient with me
Reply With Quote
  #7  
Old 10-02-2008, 01:23 PM
wayneph's Avatar
waynephHow do you prevent multiple users colliding on a single record? wayneph is offline
Web Junkie

Retired Moderator
* Expert *
 
Join Date: Apr 2004
Location: D/FW, Texas, USA
Posts: 8,393
Default

1 and 2. Yes it's a table, but no it can have multiple records. In his design, I think he expects 1 record per table. That way if a user is updating TableA table, it wouldn't block someone from updating TableB. Personally, I might take it a step further, and add a column for the specific record ID. That way multiple people can save to the same table as long as they're saving to different records.

3. Yes it is VB code, but it is not VB.NET Code. You are correct, you will just need to create an OleDbCommand that has the same SQL Statements and use the ExecuteNonQuery method. In his code, "n" gets a count of the RecordsAffected. He assumes that if 1 record is updated, then he got the Lock and he has rights on the table. Then when he's done with the update, he releases the lock by setting the flag back to 0.

My thoughts...
If you have a lot of users that will be modifying the same table, you can also add a flag to the record for "checking it out". Allow anyone to view the record, but if someone is editing it, throw their userid in a field that locks it. Then if anyone else goes to the record, say that it is read-only because "Tom" has it locked.
__________________
-- wayne, MSSM Retired
> SELECT * FROM users WHERE clue > 0
0 rows returned
Reply With Quote
  #8  
Old 10-03-2008, 08:32 AM
IndraG IndraG is offline
Junior Contributor
 
Join Date: Feb 2006
Posts: 204
Default

Dear Wayne and Roger,
Thank you so much for the ideas, very interesting. I have given it a great thought and even comes up with some ideas myself on some variations to suit my need. However, there is only a major concern. This semaphore idea is good as long as the whole process will go without interruption, ie, the flags being set to normal after the process. However, assuming something unthink of happens, say power cut off (or some silly guy kicks the power cable off, happens before!) just in the middle of the process, and the computer was unable to complete the loop and the flags are still set as if it is on lock, then nobody can access the record/table any more! What did you guys do to prevent such things (apart from kicking the butt of the silly guy)?

Thank you so much,
Indra
Reply With Quote
  #9  
Old 10-03-2008, 08:43 AM
wayneph's Avatar
waynephHow do you prevent multiple users colliding on a single record? wayneph is offline
Web Junkie

Retired Moderator
* Expert *
 
Join Date: Apr 2004
Location: D/FW, Texas, USA
Posts: 8,393
Default

Instead of using just a boolean (Locked/Not Locked) you could use a timestamp. In the semaphore case, it is just being set during the process. A single update should never take more than a couple seconds.

If you add the time of the last lock when you create the semaphore, you can add an additional check to see if it's more than 5 (or some other arbitrary number you choose) seconds old. If that's the case, I would assume that something happened to the other connection, and they're no longer updating.

Obviously you'll need to adjust the timeout based on how the code is being used. Updating thousands of records would require a longer time out than just updating one.
__________________
-- wayne, MSSM Retired
> SELECT * FROM users WHERE clue > 0
0 rows returned
Reply With Quote
  #10  
Old 10-05-2008, 06:08 PM
IndraG IndraG is offline
Junior Contributor
 
Join Date: Feb 2006
Posts: 204
Default

OK, thanks so much, guys
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
How do you prevent multiple users colliding on a single record?
How do you prevent multiple users colliding on a single record?
How do you prevent multiple users colliding on a single record? How do you prevent multiple users colliding on a single record?
How do you prevent multiple users colliding on a single record?
How do you prevent multiple users colliding on a single record?
How do you prevent multiple users colliding on a single record? How do you prevent multiple users colliding on a single record? How do you prevent multiple users colliding on a single record? How do you prevent multiple users colliding on a single record? How do you prevent multiple users colliding on a single record? How do you prevent multiple users colliding on a single record? How do you prevent multiple users colliding on a single record?
How do you prevent multiple users colliding on a single record?
How do you prevent multiple users colliding on a single record?
 
How do you prevent multiple users colliding on a single record?
How do you prevent multiple users colliding on a single record?
 
-->