Locking tables in an Oracle db from VB
Posted on 2003-12-04
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
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.