Locking Rows
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Locking Rows

  1. #1
    Join Date
    May 2003
    Posts
    49

    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

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,997
    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

  3. #3
    Join Date
    May 2003
    Posts
    49
    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

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,997
    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.

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,997
    Originally posted by slimdave
    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.

  7. #7
    Join Date
    May 2003
    Posts
    49
    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
  •  



Click Here to Expand Forum to Full Width