-
Locking Rows
Hi,
Using Oracle 9.2.1.0
I have 3 instances of a VB exe that poll for FTP'd data files. When 1 of these routines grabs an incoming file it marks up a value as having "got" the file so no other instance can get the same file.
My problem is that occasionally more than 1 instance will grab the same file.
I have tried using TABLE LOCK to no avail, but multiple instances have still been able to get the row. Can anyone see where I'm going wrong or if there is a better way of doing this?
Code:
Set rsCurrDb = New ADODB.Recordset
rsCurrDB.Open("SELECT Got_DB FROM myTbl where District = 'NE'", conn)
If rsCurrDb("Got_DB") = 0 Then
Conn.Execute ("LOCK TABLE myTbl In EXCLUSIVE MODE NOWAIT")
If last_error = 0 Then ' check For Error due To table Lock
Conn.Execute "UPDATE myTbl Set Got_DB = -1 where District = 'NE'"
Conn.Execute "COMMIT"
...
Exit Sub
ErrorHandler:
If Err.Number <> 0 Then
If InStr(Err.Description, "NOWAIT") <> 0 Then
last_error = Err.Number
Resume Next
End If
End If
Many thanks
Al
-
Why do you need to lock the entire table? Besides everytime you do a "EXCLUSIVE MODE NOWAIT" you take away the lock from the last process to get a lock. You should only lock the rows that you need to lock, by doing a for update.
Code:
Set rsCurrDb = New ADODB.Recordset
rsCurrDB.Open("SELECT Got_DB FROM myTbl where District = 'NE' FOR UPDATE", conn)
Conn.Execute "UPDATE myTbl Set Got_DB = -1 where District = 'NE'"
Conn.Execute "COMMIT"
...
Exit Sub
ErrorHandler:
If Err.Number <> 0 Then
If InStr(Err.Description, "NOWAIT") <> 0 Then
last_error = Err.Number
Resume Next
End If
End If
-
Gandolf,
I've tried what you suggested but it's not working
Code:
SELECT Got_DB FROM myDB WHERE District = 'NE'
Returns 1 record
SELECT Got_DB FROM myDB WHERE District = 'NE' FOR UPDATE
Returns 0 records??
SELECT Got_DB FROM myDB WHERE District = 'NE' FOR UPDATE OF Got_DB NOWAIT
Returns 1 record
I've tried using two different connections with two different users and it still allows me to select and update, any ideas? It seems to work through SQL Plus but not VB which is odd?
Code:
Private Sub Command1_Click()
Dim cn As ADODB.Connection
Dim cn2 As ADODB.Connection
Dim rs As ADODB.Recordset
Dim rs2 As ADODB.Recordset
Dim cnStr As String
Dim cnStr2 As String
Dim strSQL As String
Dim strSQL2 As String
strSQL = "SELECT Got_DB FROM myTbl WHERE District = 'NE' For UPDATE OF Got_DB NOWAIT"
strSQL2 = "SELECT Got_DB FROM myTbl WHERE District = 'NE' For UPDATE OF Got_DB NOWAIT"
'strSQL2 = "Update myTbl Set Got_DB = 0 WHERE District='NE'"
cnStr = "PROVIDER=MSDASQL;" & _
"DRIVER={Microsoft ODBC For Oracle};" & _
"SERVER=myDB;" & _
"UID=auser2;" & _
"PWD=auser2;"
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Open cnStr
cnStr2 = "PROVIDER=MSDASQL;" & _
"DRIVER={Microsoft ODBC For Oracle};" & _
"SERVER=myDB;" & _
"UID=user;" & _
"PWD=user;"
Set cn2 = New ADODB.Connection
Set rs2 = New ADODB.Recordset
cn2.Open cnStr2
rs.Open strSQL, cn, adOpenStatic, adLockPessimistic, adCmdText
If Not rs.EOF And Not rs.BOF Then
Debug.Print rs("Got_DB")
End If
'cn.Execute strSQL2
rs2.Open strSQL2, cn2, adOpenStatic, adLockPessimistic, adCmdText
End Sub
-
Originally posted by Running Bear
[B]
SELECT Got_DB FROM myDB WHERE District = 'NE'
Returns 1 record
SELECT Got_DB FROM myDB WHERE District = 'NE' FOR UPDATE
Returns 0 records??
SELECT Got_DB FROM myDB WHERE District = 'NE' FOR UPDATE OF Got_DB NOWAIT
Returns 1 record
Are you sure its not working? Once you do a commint, rollback or alter in a session all of the locks are released. First make sure that all of the locks have been released, then try going to sqlplus and doing "SELECT Got_DB FROM myDB WHERE District = 'NE' FOR UPDATE"
Then open another sqlplus connection and look to see that there is a lock on the mydb table. Then try to do this "Got_DB FROM myDB WHERE District = 'NE'" and see what happens.
If you are still having problems, try writing a singleton type function that returns a pointer to a single file. It could do something like "SELECT max(district) from mydb where got_db != 0 FOR UPDATE", it would then do the update and return the value it selected. There might be a problem with using ado and Oracle locking. If so, then using a PL/SQL function should resolve your issue.
-
My own preference would be to use the DBMS_LOCK package to implement a lightweight locking mechanism -- this is exactly the kind of situation that it is intended for.
http://download-west.oracle.com/docs...08sql.htm#2499
-
I agree that dbms_lock would work, however being a PL/SQL programmer
I would think that a PL/SQL solution will always be best.
-
Gandolf & SlimDave,
It definitely seems to be a problem with using it through VB, have tried it with DAO and ADO without success, but works okay through TOAD and SQL Plus.
I will look into doing it through PL/SQL or with the DBMS_LOCK
Many thanks for your help.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|