As was foretold, we've added advertisements to the forums! If you have questions, or if you encounter any bugs, please visit this thread: https://forums.penny-arcade.com/discussion/240191/forum-advertisement-faq-and-reports-thread/
Options

Removing locks on records in an Oracle DB (UPDATED 5-29-08)

whuppinswhuppins Registered User regular
edited May 2008 in Help / Advice Forum
I'm a total novice when it comes to Oracle, so please don't assume I know how to do anything. Unfortunately, I'm the one who has to deal with this current problem and as far as I can tell, the only weapon at my disposal is SQL*Plus.

We use software at work that's based on an Oracle 9 DB. The software we're working with isn't letting us update a record because it says that the record is locked by a user. I've shut down the instance, rebooted the server, and killed as many sessions as possible. There don't seem to be any lingering sessions that would be hanging onto these records.

V$LOCKED_OBJECT is empty and the only remaining sessions are unkillable (SQL*Plus spits out ORA-00029, "session is not a user session" when I try to kill them... they seem to be stemming from Windows services on the server machine). There are 30+ entries in DBMS_LOCK_ALLOCATED, though I don't know anything about that table (I just randomly found it when searching for tables with "lock" in the name) and it may be a total red herring. However, the entries therein do appear to be related to our software.

So, how do I go about unlocking records? Deleting from DBMS_LOCK_ALLOCATED seems pretty cavalier, and of course it may not actually remove the lock. Thoughts?

whuppins on

Posts

  • Options
    vonPoonBurGervonPoonBurGer Registered User regular
    edited May 2008
    Ok, first of all, v$locked_object is probably not what you want to look at. That's the list of locked objects, i.e. tables, views, indexes, stored procedures, etc. Individual rows aren't going to appear in that view. If it's actually a row-level Oracle lock (which I doubt, more on that later), you'd be able to see it in this view:
    select * from dba_locks where blocking_others <> 'Not Blocking';
    
    If you don't have any sessions that are blocking other sessions, you have no Oracle locks to worry about.

    To be honest, I'd bet a bazillion dollars that the lock isn't at the Oracle level, it's at the application level. Oracle doesn't even have an error message for "row is locked" as far as I'm aware. Your session will simply wait (forever, if necessary) for the other session to commit and release its lock. Unless a deadlock is detected, in which case you get a "deadlock detected" error, but that's a pretty special case where two sessions are waiting on each other. Does your message have an error number that starts with ORA-, TNS-, or something similar, followed by five digits? If not, it's almost certainly not an Oracle error message per se.

    Because of this Oracle behavior (no error on finding a row is locked, wait forever), application developers will often craft their own locking solution on top, to prevent their applications from waiting forever in certain lock situations. I know the last application I worked with did this, and sure enough the application's locking scheme had plenty of bugs. I'd inspect the application's schema, looking for any flags in tables that seem to be related to locking, or any tables where locks are tracked. Alternately, the locking may be entirely held in memory by the application services, in which case restarting those services might do the trick. It's really hard to say what the fix would be, because app-specific locking mechanisms are entirely up to the app developer.

    vonPoonBurGer on
    Xbox Live:vonPoon | PSN: vonPoon | Steam: vonPoonBurGer
  • Options
    whuppinswhuppins Registered User regular
    edited May 2008
    Thanks for replying; you've given me a lot to mull over. Here are some thoughts:
    Ok, first of all, v$locked_object is probably not what you want to look at. That's the list of locked objects, i.e. tables, views, indexes, stored procedures, etc. Individual rows aren't going to appear in that view. If it's actually a row-level Oracle lock (which I doubt, more on that later), you'd be able to see it in this view:
    select * from dba_locks where blocking_others <> 'Not Blocking';
    
    If you don't have any sessions that are blocking other sessions, you have no Oracle locks to worry about.
    That returns no rows, but I ran a similar(?) query earlier that I found after Googling locking issues. Some entries seem odd (XR and Temp Segment...?), but nothing's blocking, as you can see:
    [html]SQL> SELECT session_id,lock_type, mode_held, mode_requested, blocking_others, lock_id1 FROM dba_lock;

    SESSION_ID LOCK_TYPE MODE_HELD MODE_REQUE BLOCKING_OTHERS LOCK_ID1





    2 Media Recove Share None Not Blocking 201
    2 Media Recove Share None Not Blocking 18
    2 Media Recove Share None Not Blocking 17
    2 Media Recove Share None Not Blocking 16
    2 Media Recove Share None Not Blocking 15
    2 Media Recove Share None Not Blocking 14
    2 Media Recove Share None Not Blocking 13
    2 Media Recove Share None Not Blocking 12
    2 Media Recove Share None Not Blocking 11
    2 Media Recove Share None Not Blocking 10
    2 Media Recove Share None Not Blocking 9
    2 Media Recove Share None Not Blocking 8
    2 Media Recove Share None Not Blocking 7
    2 Media Recove Share None Not Blocking 6
    2 Media Recove Share None Not Blocking 5
    2 Media Recove Share None Not Blocking 4
    2 Media Recove Share None Not Blocking 3
    2 Media Recove Share None Not Blocking 2
    2 Media Recove Share None Not Blocking 1
    3 Redo Thread Exclusive None Not Blocking 1
    4 XR Null None Not Blocking 4
    5 Temp Segment Row-X (SX) None Not Blocking 2

    22 rows selected.[/html]
    To be honest, I'd bet a bazillion dollars that the lock isn't at the Oracle level, it's at the application level. Oracle doesn't even have an error message for "row is locked" as far as I'm aware. Your session will simply wait (forever, if necessary) for the other session to commit and release its lock. Unless a deadlock is detected, in which case you get a "deadlock detected" error, but that's a pretty special case where two sessions are waiting on each other. Does your message have an error number that starts with ORA-, TNS-, or something similar, followed by five digits? If not, it's almost certainly not an Oracle error message per se.

    Actually, I had the same feeling when I first started seeing the message, despite knowing little to nothing about Oracle. Usually when there's an Oracle error, the app will show an error message that's basically just the Oracle error message reproduced verbatim, including whatever ORA- codes are involved. This one seems to be a purely app-side message. It gives no specific details (basically just, "Sorry, that item is locked") and doesn't even make any references to Oracle. It was for this reason that I went to the software vendor for the initial support, but I was kind of thrown off the trail when they came back with a bunch of stuff about killing Oracle sessions. The more I've studied this, the more I realize that (surprise, surprise) the support guy didn't have a clue, assumed it wasn't his software's fault, and just ended up Googling for Oracle solutions.
    Because of this Oracle behavior (no error on finding a row is locked, wait forever), application developers will often craft their own locking solution on top, to prevent their applications from waiting forever in certain lock situations. I know the last application I worked with did this, and sure enough the application's locking scheme had plenty of bugs.

    So, like, developers will put in some sort of timeout mechanism in the software that will wait x seconds before stepping in? Is that what you're talking about? This message appears pretty much instantly; would that suggest that this isn't what's going on in my case?
    I'd inspect the application's schema,

    Ummmmm... :oops: mind explaining 'schema'? I know, I know, this is some basic-level Oracle terminology. I told you I was a novice.
    looking for any flags in tables that seem to be related to locking, or any tables where locks are tracked.

    I've been trying to do this, but the only trick I know is
    select table_name from all_tables where upper(table_name) like '%LOCK%';
    

    ...It hasn't gotten me very far, unfortunately. I'm planning on making another support call that's basically, "Look, it's your software's problem, not Oracle's, now put me in touch with someone who can explain the locking functionality to me in detail." Until then, do you have any other ideas for sniffing out where this stuff might be buried?
    Alternately, the locking may be entirely held in memory by the application services, in which case restarting those services might do the trick.

    Again, sorry for my ignorance on the subject, but wouldn't rebooting the server have covered this?
    It's really hard to say what the fix would be, because app-specific locking mechanisms are entirely up to the app developer.

    Yeah, but we pay good money for a support contract, so as soon as I can convince them that the problem's on their end, I shouldn't have to be the one to come up with the solution. This information will help greatly toward that effort :) Thanks again for taking the time to help.

    whuppins on
  • Options
    vonPoonBurGervonPoonBurGer Registered User regular
    edited May 2008
    whuppins wrote: »
    That returns no rows, but I ran a similar(?) query earlier that I found after Googling locking issues. Some entries seem odd (XR and Temp Segment...?), but nothing's blocking, as you can see:
    [html]SQL> SELECT session_id,lock_type, mode_held, mode_requested, blocking_others, lock_id1 FROM dba_lock;

    SESSION_ID LOCK_TYPE MODE_HELD MODE_REQUE BLOCKING_OTHERS LOCK_ID1





    2 Media Recove Share None Not Blocking 201
    2 Media Recove Share None Not Blocking 18
    2 Media Recove Share None Not Blocking 17
    2 Media Recove Share None Not Blocking 16
    2 Media Recove Share None Not Blocking 15
    2 Media Recove Share None Not Blocking 14
    2 Media Recove Share None Not Blocking 13
    2 Media Recove Share None Not Blocking 12
    2 Media Recove Share None Not Blocking 11
    2 Media Recove Share None Not Blocking 10
    2 Media Recove Share None Not Blocking 9
    2 Media Recove Share None Not Blocking 8
    2 Media Recove Share None Not Blocking 7
    2 Media Recove Share None Not Blocking 6
    2 Media Recove Share None Not Blocking 5
    2 Media Recove Share None Not Blocking 4
    2 Media Recove Share None Not Blocking 3
    2 Media Recove Share None Not Blocking 2
    2 Media Recove Share None Not Blocking 1
    3 Redo Thread Exclusive None Not Blocking 1
    4 XR Null None Not Blocking 4
    5 Temp Segment Row-X (SX) None Not Blocking 2

    22 rows selected.[/html]
    You're always going to have some locks in the system. Just by looking at the session IDs, I can tell those are probably all internal Oracle processes. There are something like 8 processes that make up the DB instance, and they always have session IDs under 10. Session 2 is probably DBWR (the process responsible for writing data to disk), 3 is probably LGWR (the process responsible for managing redo logs). You can always check and see what those sessions are, the SESSION_ID in the DBA_LOCK view is the same value as the SID in the V$SESSION view.
    whuppins wrote: »
    So, like, developers will put in some sort of timeout mechanism in the software that will wait x seconds before stepping in? Is that what you're talking about? This message appears pretty much instantly; would that suggest that this isn't what's going on in my case?
    It could be timeout, but it could be just a row in a table somewhere that says "Customer record X is locked by user Y". The software will check that custom app lock table (or field) before it will let you modify that record. The problem with the row-in-a-table method is that eventually some process somewhere crashes and doesn't clean up its locks, and you have to go in and manually clean up the leftover junk.
    whuppins wrote: »
    Ummmmm... :oops: mind explaining 'schema'? I know, I know, this is some basic-level Oracle terminology. I told you I was a novice.
    Heh, sorry. Schema = "all the junk owned by a user". So if you have a DB user named FOO, then all the objects (tables, indexes, stored procedures, etc.) owned by FOO are, collectively, the FOO schema. Usually you'll have a diagram for all the table relationships within a schema, showing tables, the columns in those tables, which ones are primary keys, which ones have indexes, and most importantly which ones are foreign key columns and what primary keys they reference. Most DBAs would refer to that as "the schema diagram". If this company can't effectively support their own product, tell them you want the schema diagram and any and all documentation they have on the database. Having that stuff makes it a lot easier to understand the relationships within the DB. Of course, you might want to keep trying to get them to fix their problem first. :)
    whuppins wrote: »
    looking for any flags in tables that seem to be related to locking, or any tables where locks are tracked.
    I've been trying to do this, but the only trick I know is
    select table_name from all_tables where upper(table_name) like '%LOCK%';
    
    ...It hasn't gotten me very far, unfortunately. I'm planning on making another support call that's basically, "Look, it's your software's problem, not Oracle's, now put me in touch with someone who can explain the locking functionality to me in detail." Until then, do you have any other ideas for sniffing out where this stuff might be buried?
    Well, they may not have made a separate table for locks, it could be a column in the table itself, with a value that the software toggles to indicate if a particular row is locked or not. You can use this query to show you all the columns that contain the word lock, and what table they reside in:
    select owner, column_name, table_name from all_tab_columns where column_name like '%LOCK%' and owner not like 'SYS%';
    
    I'm assuming with that query that the application stores its data under one or more users that aren't SYS or SYSTEM. And shame on the developers if that ain't true. Also, maybe they're dicks and they've given the table or column a name that doesn't really reflect its purpose (i.e. doesn't contain the word "lock"). You never know.
    whuppins wrote: »
    Alternately, the locking may be entirely held in memory by the application services, in which case restarting those services might do the trick.
    Again, sorry for my ignorance on the subject, but wouldn't rebooting the server have covered this?
    I would imagine so, yes. Sorry, didn't realize you'd already tried the reboot. :)
    whuppins wrote: »
    Yeah, but we pay good money for a support contract, so as soon as I can convince them that the problem's on their end, I shouldn't have to be the one to come up with the solution. This information will help greatly toward that effort :) Thanks again for taking the time to help.
    Hey, no problem. It definitely doesn't sound like an Oracle problem, and more of an application thing, so I'd verbally beat 'em about the head, face and neck until they agree to take a look. Application lock issues were utterly common on one of the Oracle-backed applications I've worked on, so if you're talking to someone who isn't just a support flowchart monkey I have to imagine they ought to know what the problem is and how to fix it. And even then, a flowchart monkey should have little boxes on his flowchart that cover application-level locks.

    vonPoonBurGer on
    Xbox Live:vonPoon | PSN: vonPoon | Steam: vonPoonBurGer
  • Options
    whuppinswhuppins Registered User regular
    edited May 2008
    Necroposting here with an update. After a few weeks of more back-and-forth with support, they want to look at our logs, copies of our system configuration, and a bunch of other stuff. I may not know much about Oracle, but I can tell that they're barking up the wrong tree. They have no idea how we're using the software and what our procedures are, they won't be able to reproduce the problem, and technically, all I originally asked for was a detailed description of their locking mechanism, not an actual solution. The closest I ever got to an answer to this was the following:
    Nautilus uses an Oracle mechanism to lock results in the database.

    In fact it uses the following SQL sentence to lock the rows in the database and prevent other users from updating them:

    SELECT 'X' from result where result . test_id IN ( <test_id> ) FOR UPDATE NOWAIT

    When another session tries to lock this row it will get an oracle error (ORA-00054: resource busy and acquire with NOWAIT specified) saying that this resource is busy.

    The reason you don't get this message in Nautilus is because the application substitute the Oracle error message with the following one:

    (pic of the program's custom error message that I've been getting)
    Note: Nautilus is the name of the software I'm dealing with. It's a LIMS, meaning it's used in a lab to track sample info and record results. This is why you see a "result" table which links to a "test" table. The locking feature is intended to ensure that multiple techs don't overwrite each other's results if they both happen to be doing result entry on the same batch of samples at the same time. Our problem is basically that it's not letting people enter results because it thinks that someone else has already locked that batch for result entry, even when no one else is on the system.

    I'm going to try to steer them away from attempting to reproduce my problem and back toward just explaining how their software works. In the meantime, does the above e-mail snippet make sense to anyone? I'm not sure what the "FOR UPDATE NOWAIT" bit at the end does, but I'm sure it's something significant. If you're able to grasp what's going on, can you try to explain it to me? If not, what kind of questions should I ask the support guy in order to get a clearer view of how the results are getting locked? Thanks.

    whuppins on
Sign In or Register to comment.