Pessimistic Locking

[hussux]

HELP!
I need to know if Pessimistic locking is possible in Oracle, and if so how do you implement it???

Reason being is I need to implement record locking in to multi-user environment, so that when a user has a record open - no other user can access it until the user has 'moved' away from the record.

techy bits: Using Oracle 8i, Visual Basic 6, ADO direct connection.

any help is greatful!

 

[jimbopalmer]

The most pessimeistic record locking I am aware of is select for update. Any user who even bring up a screen locks all other users from that record. It frequently brings my system to it's knees, even though it was used on only one minor screen.

Other than that mistake, Oracle implements row locking very well by default. I tried to remain child-like, all I acheived was childish.

 

[hussux]

i've tried the Select for update statement without success,

SELECT * From myQueries where QueryNo = '5' FOR UPDATE
I get an error:-

ORA-01002: Fetch out of sequence

any suggestions?

 

[Swany]

lock table [table_name] [mode] [nowait]

modes:
ROW SHARE allows concurrent access to the locked table, but prohibits users from locking the entire table for exclusive access. ROW SHARE is synonymous with SHARE UPDATE, which is included for compatibility with earlier versions of Oracle.

ROW EXCLUSIVE is the same as ROW SHARE, but also prohibits locking in SHARE mode. Row Exclusive locks are automatically obtained when updating, inserting, or deleting.

SHARE UPDATE-see ROW SHARE.

SHARE allows concurrent queries but prohibits updates to the locked table.

SHARE ROW EXCLUSIVE is used to look at a whole table and to allow others to look at rows in the table but to prohibit others from locking the table in SHARE mode or updating rows.

EXCLUSIVE allows queries on the locked table but prohibits any other activity on it.

The nowait parameter instructs the db to return immediately with an error if another use has locks on the table. .
.. Eat, think and be merry .
... ....................... .