Solved

How can I prevent two applications from accessing/updating the same information in a database?

Posted on 2006-10-24
7
258 Views
Last Modified: 2010-04-23
I am working on a VB.Net application that uses the same database as another application (Access 2000).  The problem I am having with my program is occasionally both programs try to access/update the same table(s) at the same time.  Can anyone suggest a way I can prevent that situation from happening in my program?
0
Comment
Question by:steverguy
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 13

Expert Comment

by:newyuppie
ID: 17801253
the subject of concurrency is very very wide to post in its entirety here. ill provide 2 useful links and maybe you can draw conclusions. or maybe you can try to narrow your question down to a specific technical problem when applying these suggestions.

http://msdn2.microsoft.com/en-US/library/cs6hb8k4.aspx
http://msdn.microsoft.com/msdnmag/issues/04/09/DataPoints/
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=232672&SiteID=1

or search the MSDN for data concurrency
0
 
LVL 21

Expert Comment

by:theGhost_k8
ID: 17801534
well newyupie have posted LINKs ...
i thought let me put an idea... ;)
ok here we go...
why not use to IPC(oops !!! how come that comes into picture)

App A                               App B
------------------------------------------
Uz DB            ------>          flag set
finished uz DB ------->        flag unset

now based on flags .. it will access the db.. if SET then wait till it becomes UNSET
wanna learn IPC:- here is the link V.Ezy....  http://www.codeproject.com/Purgatory/KzIPC.asp
i found its the best way to interact betn applications ..
well if you could find any Prog. option other than this WORKING pls post...
 we were using something like  "adopendynamic/ adlockoptimistic".. try that thing.... i dont remember though :))
0
 
LVL 10

Expert Comment

by:Kinger247
ID: 17802296
Yes I agree with newyuppie, concurrency/locking is the only way to do this .. or amend the way your program operates.
Setting flags is doomed to fail :(
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 1

Author Comment

by:steverguy
ID: 17804343
I have something I want to clarify as far as the locking is concerned.  I have control over only one of the programs that uses the database.  The other program was written and compiled by another company.  Will using lock optimistic or lock pesimistic still be effective if one of those locking methods is being used by only one program?
0
 
LVL 10

Expert Comment

by:Kinger247
ID: 17804843
As far as I know, your application instructs the database to lock in a specific way. So its the database locking the tables not your app.
Thats how I understand it.
0
 
LVL 1

Author Comment

by:steverguy
ID: 17804933
Is there some way to detect whether or not the other application has locked a table or record?
0
 
LVL 13

Accepted Solution

by:
newyuppie earned 500 total points
ID: 17805730
in most cases if you apply a concurrency locking strategy on your application, and especially if you are using 2005, it will depending on the strategy, deal with the locking automatically.
moreover, some database servers (MySQL for instance) provides row and table locking strategies as well. you can instruct MySQL to lock a table when you are reading, and/or updating it. when some other app tries to access the db, they will have to wait until you are finished with the table. same with your app, if another app has the lock on the table, you will need to wait. most of the times there is no need to have specific methods for waiting around in your application. generally you will such a strategy that your program will issue the command to the database and the database itself will handle when to run the command, like Kinger comments (once the lock is off).

its not so hard to understand if you read the proper documentation, as it is to explain months of testing and programming in 1 single answer to a very general question.

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now