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

[bjackson]

Here's the setup:&nbsp;&nbsp;This problem concerns locking a given record from the application.&nbsp;&nbsp;The application is written in C++ (MFC).&nbsp;&nbsp;The primary problem seems to be that the Oracle ODBC driver doesn't support dynamic or dynaset type recordsets.&nbsp;&nbsp;We can only select stuff as forward only, and static.<br><br>User A opens a record with the intent of updating them.&nbsp;&nbsp;While User A is making modifications to the record, User B opens the same record with the intent of updating it.&nbsp;&nbsp;User A finishes his modifications, and commits the record.&nbsp;&nbsp;User B finishes his modifications, and commits the record, therby nuking everything User A has just saved.<br><br>I can construct my SQL statement using the &quot;FOR UPDATE OF ColumnName NOWAIT&quot; clause, and this _kind of_ works.&nbsp;&nbsp;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.&nbsp;&nbsp;At this point, User B has to refetch the record, and consequently loses all of his modifications.&nbsp;&nbsp;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).<br><br>Any thoughts?&nbsp;&nbsp;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!&nbsp;&nbsp;&lt;grrrrrrr!&gt;<br><br> <p>Beau Jackson<br><a href=mailto:bjackson@bloomington.in.us>bjackson@bloomington.in.us</a><br><a href= > </a><br>

 

[massonek]

This can be a frustrating issue, but here's some info that might help.<br><br>Every time a user locks a table for DML ( insert, update, delete ) an entry will appear in v$lock.&nbsp;&nbsp;This view contains a column called id1, which is the object id of that table.&nbsp;&nbsp;As soon as the user commits or does a rollback, this entry will be removed.<br><br>The following query will tell you if a table has acquired a DML lock and a commit or rollback is pending.<br><br><FONT FACE=monospace><br>select DECODE ( count(*), 0, 'No&nbsp;&nbsp;&nbsp;&nbsp;',<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Yes&nbsp;&nbsp;&nbsp;'&nbsp;&nbsp&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&quot;Locked&quot;<br>from&nbsp;&nbsp;&nbsp;dba_objects&nbsp;&nbsp;&nbsp;obj,<br>(<br>select id1<br>from&nbsp;&nbsp;&nbsp;v$lock<br>)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;lck<br>where&nbsp;&nbsp;obj.object_id&nbsp;&nbsp;&nbsp;&nbsp;=&nbsp;&nbsp;lck.id1<br>and&nbsp;&nbsp;&nbsp;&nbsp;obj.object_type&nbsp;&nbsp;= 'TABLE'<br>and&nbsp;&nbsp;&nbsp;&nbsp;obj.object_name&nbsp;&nbsp;= 'YOUR_TABLE_NAME_HERE'<br>/<br></font><br><br>The SQL syntax uses an in-line view for performance reasons, but there may well be a better way to write it.<br><br>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.&nbsp;&nbsp;There may be other issues or unintended side-effects I haven't thought of, but hopefully this is of some use.&nbsp;&nbsp;Now you just have to make it all work with C++ and ODBC.&nbsp;&nbsp;Plus deal with the security implications of using V$ views.<br>