shayb022800
asked on
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?
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?
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
http://www.datadirect-technologies.com/download/docs/ado/coleref/lock.htm
http://www.emu.edu.tr/english/facilitiesservices/computercenter/bookslib/Teach%20Yourself%20DATABASE%20PROGRAMMING%20WITH%20VB5%20in%2021%20Days,%202nd%20Ed/htm/ch17.htm
http://cntt.vdc.com.vn/bookstore/Platinum%20Edition%20Using%20Visual%20Basic%205/ch34/ch34.htm