Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Locking tables in an Oracle db from VB

Posted on 2003-12-04
3
Medium Priority
?
323 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: 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

Independent Software Vendors: 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

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
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 Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…
Suggested Courses

824 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