java database unique constraint問題

twaiho2003

我係用spring data 的, 但係個問題都適用一般jdbc
我想問如果insert record果陣違反左unique constraint, 應該點樣由個exception搵返邊個field違反
我insert之前已經先check左有冇exist,  但係如果兩個thread 同時insert 同一款data, 就check唔到
到時就一定會error, 然後exception,
但係google左好耐都冇方法由個exception搵返邊個field.

twaiho2003

岩岩再google多陣，如果寫個@service
先check有冇exist, if no, insert, else return which field duplicate

Spring @service好似係single thread的
唔知咁樣得唔得呢？

望月小妖

我唔識Spring，都難知邊個fields，Oracle就grep 個ORA-00001 exception 拆個constraint 名出嚟再query個 all_constraints / user_constraints table

twaiho2003

回覆 3# 望月小妖


    比著你會點handle insert account 果陣，username or email duplicate.
真係要寫regex拆個constraint name？

alex959neth

回覆 1# twaiho2003

Actually you may handle this kind of race condition properly by using Pessimistic locking or Optimistic Locking.

you may have a look:

http://stackoverflow.com/questio ... pring-and-hibernate

mayday

回覆  twaiho2003

Actually you may handle this kind of race condition properly by using Pessimistic ...
alex959neth 發表於 2016-2-15 23:32

好似只適合update，冇講insert得唔得。

望月小妖

其實你用咩DBMS？用stored procedure check得唔得？

tuyylihk

呢d野梗係用返sql解決,點會係java度解....

硬係要搞真係只可以係個exception抽返個field出黎
但我冇記錯架話exception好多時只係講第幾個field........

其實既然本身就有咁既考慮
不如乾脆係insert前先check入既野有冇撞現有data,同埋本身堆data有冇duplicate
===============================
噢岩岩見到multi-thread

但就又有個問題,你想點樣handle先
一個thread死就成個thread既野唔insert?
一個thread死只唔insert個一條?
commit point呢?

唔理咩方法都好,點都加返個lock會好d
你係咪真係要完全parallel insert?

twaiho2003

我用mysql, 如果用command line insert duplicate record
出Duplicate entry 'a' for key 'UK_979xvypjc2lwr1ia4kq77cko0'
Java exception 最有用果句getMessage()都係呢句
唔知PHP,PYTHON, .NET 各款mysql driver , 係咪都係return呢句？
唔知有冇一個error message 係programming friendly,

我覺得用java做任何checking都係重履, 我係mysql set左unique constraint,
佢內部已經會check

rabbit82047

如果一定要用 thread 而又一定唔可以有 duplicate
你不如將 checking 同 insert 放入同一個 synchronized method 黎做好過
Performance 就唔好諗太多啦

Check exception 走遠路，俾我唔會向呢個方向諗

補充, phantom read 都要考慮，另一個方法就係 gen 完要 insert 既 data 統一做 checking 然後 batch insert