Home » SQL & PL/SQL » SQL & PL/SQL » Lock row and notify to other computer
icon6.gif  Lock row and notify to other computer [message #235554] Mon, 07 May 2007 03:03 Go to next message
nghiant
Messages: 38
Registered: July 2006
Location: Viet Nam
Member
Hello everybody Laughing
Thanks for help me last time Laughing . Now I have a problem and need help from you. Embarassed
In my case, I have more records is displaying in a grid, and all record is get in STUDENT table.
When I select a record in my computer, consider it is locked.
In this time, has other computer select same row as my computer, my program must show message: "this record is selected in diff computer".
Razz Razz Razz Razz Razz Razz Razz Razz Razz Razz Razz
I have a solution: when user select a record, I use a status column in STUDENT table to notify it is selected. But it is not good solution, in Oracle has anyway to do?

[Updated on: Mon, 07 May 2007 03:10]

Report message to a moderator

Re: Lock row and notify to other computer [message #235558 is a reply to message #235554] Mon, 07 May 2007 03:22 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Do a SELECT FOR UPDATE NOWAIT:
Session 1:
SES1>SELECT city
  2       , street_address
  3       , postal_code
  4  FROM   locations
  5  WHERE  country_id = 'US'
  6  FOR UPDATE NOWAIT
  7  /

CITY                      STREET_ADDRESS                           POSTAL_CODE
------------------------- ---------------------------------------- ------------
Southlake                 2014 Jabberwocky Rd                      26192
South San Francisco       2011 Interiors Blvd                      99236
South Brunswick           2007 Zagora St                           50090
Seattle                   2004 Charade Rd                          98199
Session 2:
SES2>SELECT *
  2  FROM   locations
  3  WHERE  postal_code = '26192'
  4  FOR UPDATE NOWAIT
  5  /
FROM   locations
       *
ERROR at line 2:
ORA-00054: resource busy and acquire with NOWAIT specified

Now if, Session 1 ends its transaction (rollback/commit), session 2 can select the data.

MHE

Re: Lock row and notify to other computer [message #235575 is a reply to message #235558] Mon, 07 May 2007 04:53 Go to previous message
nghiant
Messages: 38
Registered: July 2006
Location: Viet Nam
Member
Thanks for your response.

I was check and it is answer. I found article: http://orafaq.com/node/854 give more information about current row was lock.
 select do.object_name,
 row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,
 dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
 from v$session s, dba_objects do
 where 
 s.ROW_WAIT_OBJ# = do.OBJECT_ID ;

---------------------------------------------
select * from objectname(xxx) where rowid = 'AAAVXxAAFAABJOKAAC'


Cool Cool Cool Cool Cool Cool Cool Cool Cool Cool
And,
In above question, if data isn't get from a STUDENT table, for intance: data is get from 2 table STUDENT - DEPARTMENT and have more computers is selecting rows that can I get all rows is locked?

I was check similar with article but I cann't get detail lock rows.

[Updated on: Mon, 07 May 2007 21:29]

Report message to a moderator

Previous Topic: MV/snapshot refresh
Next Topic: how to use a cursor so as it selects value from another cursor
Goto Forum:
  


Current Time: Sun Dec 11 05:56:43 CST 2016

Total time taken to generate the page: 0.24381 seconds