Solved

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

Posted on 2006-10-24
7
259 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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

786 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