Solved

Locking tables in an Oracle db from VB

Posted on 2003-12-04
3
306 Views
Last Modified: 2013-12-25
I am trying to parallelize my application. In order to do so I have to make sure that sequences taken by different processes are not the same. I thought about using a lock mechanism on a dummy table to pause the 2nd process while the 1st is getting the sequence.
I have tried running the following code from two machine simultaneously and didn't get a lock (perhaps since I'm working through ODBC);

'lock the seq with dummy table for multi users
    sqlLock = "select seq_name from " & DBNAME & ".dummy where seq_name='A_SEQ' for update;"
    Set rsLock = New ADODB.Recordset
    LogFile.WriteLine "locked table " & Now
    OpenRecordset sqlLock, rsLock
    LogFile.WriteLine "in lock" & Now
'get a sequences
    sql = "alter sequence " & DBNAME & ".m_seq increment by " & (nNum) & ";"
'release the loc
    rsLock.Cancel
    rsLock.Close

The sequence should have been different in the two computers by 'nNum' but instead I get a difference of one. The time stamps in the 'LogFile' also indicate both machines accessed the seq together.

any suggestions?
0
Comment
Question by:shayb022800
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
3 Comments
 
LVL 8

Expert Comment

by:MYLim
ID: 9879095
0
 
LVL 8

Expert Comment

by:MYLim
ID: 9879098
ALL YOU HAVE TO DO ARE SET LockType to AdLockPesismistic . (this is an bad idea if you want to lock whole table,server will result heavy duty).

EX:you have a tablename TABLE1 and Fieldname MyNUMBER and 5 record inside.
==============
MyNumber
1,2,3,4,5
==============
Just change your String SQL to select record that you want to lock.
1.lock only one record - record will lock = 1:

 rstEmployees.CursorType = adOpenDynamic
 rstEmployees.LockType = adLockPesimistic
 rstEmployees.Open "select * from Table1 where MyNumber = 1", , , , adCmdText


2.lock a block of record-record will lock = 3:

 rstEmployees.CursorType = adOpenDynamic
 rstEmployees.LockType = adLockPesimistic
 rstEmployees.Open "select * from Table1 where MyNumber >=3", , , , adCmdText

3.lock whole table-records will lock = every record inside TABLE1 :

 rstEmployees.CursorType = adOpenDynamic
 rstEmployees.LockType = adLockPesimistic
 rstEmployees.Open "select * from Table1", , , , adCmdText
0
 
LVL 8

Accepted Solution

by:
MYLim earned 250 total points
ID: 9879101
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

751 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