Thanks for the question, M.
Asked: October 24, 2011 - 8:38 am UTC
Last updated: October 25, 2011 - 9:39 am UTC
Version: 11g
Viewed 1000+ times
You Asked
Hello Tom,
I have a CUSTOMERS table where users upload large sets of data (500,000-1,000,000 records) from external tables (CSV files).
This is the SQL statement:
INSERT INTO Customers (SELECT * FROM external_table)
When uploading data, users pick-up the following values from an interface, from drop-down boxes:
REPORT_CYCLE (a set of 10 values)
REPORT_MM (a DATE)
PLATFORM_ID (a set of 5 values)
ADDRESS_TYPE (can be 'S' or 'B')
This combination must be unique for every batch of data that gets inserted into CUSTOMERS table.
In a single user environment it works fine.
However, in a multi-user environment, let's consider the following scenario:
1. User A wants to insert a new batch. Therefore I perform a check, something like:
SELECT report_cycle,report_mm, etc... count(*) FROM customers WHERE report_cycle = <value>, etc... GROUP BY report_cycle, etc HAVING count(*) > 0
The count is 0 so the import starts. It may take around 1 minute
2. Meanwhile, user B wants to insert a batch with the same \REPORT_CYCLE + REPORT_MM + PLATFORM_ID + ADDRESS_TYPE combination.
If the transaction started above did not commit, then the count will be 0 as well and the second user will start the import.
This is incorrect. I need to prevent other users to insert records having similar REPORT_CYCLE + REPORT_MM + PLATFORM_ID + ADDRESS_TYPE.
Can you please advise what would be the most efficient way to implement this functionality?
Regards,
M.R.
and Tom said...
You should do what was suggested on the OTN forums where you posted this initially!
https://forums.oracle.com/forums/thread.jspa?threadID=2300689&start=0&tstart=0 specifically this one:
https://forums.oracle.com/forums/thread.jspa?threadID=2300689&start=0&tstart=0#9944341 You need another table - that table will be the parent table for the customers table you are loading into. That table will have simply those four columns and a primary key on them.
In that fashion - only one user will be able to insert a row into this parent table with those key values - anyone else would be blocked from doing so.
If you don't want the other user to block, you could use logic with dbms_lock to acquire a named lock - a lock named after the four values in question. If the lock was already taken - you would fail immediately. If the lock was not taken - you could do your insert (knowing it will not block) and continue out.
You could fashion a trigger on this parent table that resembles this:
scott%ORA11GR2> create or replace trigger demo_bifer
2 before insert on demo
3 for each row
4 declare
5 l_lock_id number;
6 resource_busy exception;
7 pragma exception_init( resource_busy, -54 );
8 begin
9 l_lock_id :=
10 dbms_utility.get_hash_value( to_char( :new.x ), 0, 1024 );
11 if ( dbms_lock.request
12 ( id => l_lock_id,
13 lockmode => dbms_lock.x_mode,
14 timeout => 0,
15 release_on_commit => TRUE ) not in (0,4) )
16 then
17 raise resource_busy;
18 end if;
19 end;
20 /
You would replace :new.x with a concatenation of your four fields.
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment