Looks like a very strange design to me.Do you have an idea how it should be?
Are you only storing the configurations and not retireving them again?Of course I am retrieving them.
How do you retrieve a configuation without knowing the values stored in them?I don't need values I have configuration ID (column config.ID) and set of key/value associated with this ID (column property.config). Configuration linked with other table by its ID.
And if you know the values of a configuration, why do you want to retrieve that configuration?For example to find out all configurations with specified key and value
- When you say two "Configs" are equal, do you mean they have the same name and the same properties?Exactly
- Does the HASH cover the name and all the properties?Yes
- create a unique constraint on CONFIG.NAMEUnfortunately can't, see first question
Third, let me suggest that you rethink your data model to make it relational. PROPERTY has an "entity/attribute/value" design, which is not relational and which creates problems of data integrity (as you are seeing) and performance.What exactly do you suggest?
Lastly, PL/SQL provides the DBMS_LOCK package. If you insist on keeping this data model, just serialize at the HASH level and you will avoid the duplicates.Sure, I have been investigation this already but what does mean "at the HASH level"?
You missed (probably) the part that a config name is uniqueConfig names are NOT unique otherwise there are no any problem =)
You could be missing a part the config name, key combination is uniqueCombination of name+keys+values is unique but just name+keys
In your logic all these "configurations" are different and storedYes, they are different
In your logic you could be missing a part about changing a key value.Name, keys and values are immutable
Denis Kishenko wrote:You need to understand how Oracle handles concurrency.
Guy lets simplify case. One table with unique column. Task : Add row if it doesn't exists.
Denis Kishenko wrote:define this query with kewords FOR UPDATE to lock through a SELECT-statement like follows:
testconfig AS (
SELECT id, name||';'||properties as text FROM config
LEFT JOIN ( -- left joint used because of configuration may don't have key/value but just name
LISTAGG(key||'='||value,';') WITHIN GROUP (ORDER BY upper(key)) properties
FROM config c, property p WHERE p.config = c.id GROUP BY c.id
) ON id = config_id
WHERE id IN (SELECT id FROM config WHERE hash = l_testconfig_hash)
SELECT id INTO l_testconfig_id FROM testconfig WHERE text = l_testconfig;
set serveroutput on declare str_to_compare varchar2(1000); begin --this select statement locks now your row with given id, so that another sessions must wait on this place for i in (select * from config where id in (select id from config where hash = 0) for update ) loop for j in ( SELECT p.config config_id, LISTAGG(key||'='||value,';') WITHIN GROUP (ORDER BY upper(key)) properties FROM property p WHERE p.config = i.id GROUP BY p.config ) loop str_to_compare := i.name || ';' ||j.properties; --do your business logic end loop; end loop; commit; --don't forget commit to unlock end;
Merge may be atomic, but you whole process of inserting or retrieving a config id needs to be atomic as far as I understood it. And that can't be solved by a simple merge.Why? As I mentioned before config+properties are immutable so retrieving is always thread-save, isn't? Merge makes thread-safe insering if required (add row if it doesn't exists), after merge I can quaranty that config is in the table. This is classical double-check-with-lock pattern.
What about a unique key on config name and hash? What are the chances you will get a hash collision with a SHA-256 hash and your dataset?Almost any hash is not uniqie in common case. Without merge even unique name doesn't solve thread-safe inserting I have provided examples above.
Denis Kishenko wrote:Denis, my suggestion is only the idea, you can implement it with your business logic.
Bawer for update locks existing records but doesn't lock inserting