Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Locking tables in an Oracle db from VB

Posted on 2003-12-04
3
Medium Priority
?
320 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: 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 1000 total points
ID: 9879101
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…
Suggested Courses

688 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