java database unique constraint問題

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

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

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

TOP

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

TOP

回覆 3# 望月小妖


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

TOP

回覆 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

TOP

回覆  twaiho2003

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

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

TOP

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

TOP

本帖最後由 tuyylihk 於 2016-2-16 10:37 編輯

呢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?

TOP

我用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

TOP

本帖最後由 rabbit82047 於 2016-2-17 22:38 編輯

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

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

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

TOP