Locking tables in an Oracle db from VB

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?
shayb022800Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MYLimCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.