Oracle Timestamp 1

[RayLee]

I've created a table, named PERSONAL_INFO, with following structure in Oracle 8i db:

Table : PERSONAL_INFO

FIELDS TYPE
=============================
TIMESTAMP DATE
PERSONAL_ID NUMBER(10)
ADDRESS VARCHAR2(50)
etc.....

There is an application allow users to browse, edit, and delete this table's data.

I want to make sure that concurrent edit is not allowed.
So, I've created an additional TIMESTAMP field for each record. When insert a new record, it will stored the db system time (MM/DD/YYYY hh:mm:ss).

I've also created some stored proc. to retrieve the data based on the user permission and some triggers depended on the events of INSERT, UPDATE, and DELETE.

While user is editing the record from PERSONAL_INFO and press the SAVE buttom, the application will call the UPDATE stored proc. and the UPDATE trigger will comparing the memory's TIMESTAMP and the db's TIMESTAMP. If not the same, return error message to user and abort the UPDATE process.

The accuracy of this approach is in 1 sec. level. For example, if num. of users is over 100,000 and num. of transcation per min. is over 10,000, the risk of concurrent editing is so high.

In MS SQL Server, I know that it can define a field as a timestamp. The db will increment the timestamp value automatically if process any UPDATE, INSERT, and DELETE event. So, the risk of concurrent edit is very low.

My question :
Can anyone tell me any better solution for concurrent edit for Oracle?

Thanks,
Raymond Lee

 

[MikeJones]

You could select the row out of the database "FOR UPDATE" this would cause Oracle to lock the row, I think this would solve your problem.

Also the Oracle date field is at a higher prescion than just seconds, try looking at the field with the following format mask, DD/MM/YYYY HH24:MI:SSSSS

This will give you the date to the thosandth of a second!!

HTH,

Mike.

 

[carp]

Mike -

Oracle's timestamp only goes down to a precision of a full second.
The 'SSSSS' in your format mask will append the number of seconds since midnight to your string. You can see this by entering
SELECT to_char(sysdate,'SSSSS') FROM dual;
and then executing this statement repeatedly. You will see the number only increments by one per second.

Oracle's DATE datatype consists of seven bytes: Century, Year, Month, Day, Hours, Minutes, Seconds. There are no fractions of a second stored in the Date.

 

[karluk]

You may need the exact time of the update to satisfy a business requirement but, if the only issue is resolving simultaneous updates, you really don't need to use time of day at all. All you really need is a column that contains an increasing numeric counter.

Call the column "update_seq_no" and set it = 1 when the row is first inserted. Then each update statement would increment the value by 1 and contain a where clause to make sure that update_seq_no contains the same value as when the row was first retrieved.

This technique allows you to avoid locking the row while a user is supplying update values.

 

[MikeJones]

Carp,

Thanks for that!

Mike.

 

[RayLee]

Thank you!

Regards,
Raymond

 

[listener22]

small comment to karluk's good idea -- the rowid can be stored
and compared, thus eliminating the need for an added column. Jim

oracle, vb