Solved

Locking tables in an Oracle db from VB

Posted on 2003-12-04
3
301 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
  • 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

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

Suggested Solutions

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…

920 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

13 Experts available now in Live!

Get 1:1 Help Now