Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

How can I tell if a recordset is locked before the user tries to save?

How can I tell if a recordset is locked before the user tries to save?

How can I tell if a recordset is locked before the user tries to save?

Here's the setup:  This problem concerns locking a given record from the application.  The application is written in C++ (MFC).  The primary problem seems to be that the Oracle ODBC driver doesn't support dynamic or dynaset type recordsets.  We can only select stuff as forward only, and static.

User A opens a record with the intent of updating them.  While User A is making modifications to the record, User B opens the same record with the intent of updating it.  User A finishes his modifications, and commits the record.  User B finishes his modifications, and commits the record, therby nuking everything User A has just saved.

I can construct my SQL statement using the "FOR UPDATE OF ColumnName NOWAIT" clause, and this _kind of_ works.  The drawback is that the application doesn't realise that the record is locked until User B tries to commit the record, at which point Oracle begins to make noise about the record being locked.  At this point, User B has to refetch the record, and consequently loses all of his modifications.  What I'm looking for is a way for User B to know that somebody else is modifying the record, and he can't play with it right now (i.e. it's read only).

Any thoughts?  I can't believe that I'm the first person who's had to deal with this, but I haven't been able to find a single thing that addresses this issue!  <grrrrrrr!>

Beau Jackson

RE: How can I tell if a recordset is locked before the user tries to save?

This can be a frustrating issue, but here's some info that might help.

Every time a user locks a table for DML ( insert, update, delete ) an entry will appear in v$lock.  This view contains a column called id1, which is the object id of that table.  As soon as the user commits or does a rollback, this entry will be removed.

The following query will tell you if a table has acquired a DML lock and a commit or rollback is pending.

select DECODE ( count(*), 0, 'No    ',
                             'Yes   '  )     "Locked"
from   dba_objects   obj,
select id1
from   v$lock
)                    lck
where  obj.object_id    =  lck.id1
and    obj.object_type  = 'TABLE'
and    obj.object_name  = 'YOUR_TABLE_NAME_HERE'

The SQL syntax uses an in-line view for performance reasons, but there may well be a better way to write it.

It's probably possible to wrap this query in a stored procedure with table name as a parameter and returning a true or false value.  There may be other issues or unintended side-effects I haven't thought of, but hopefully this is of some use.  Now you just have to make it all work with C++ and ODBC.  Plus deal with the security implications of using V$ views.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close