I am developing an Erlang application which requires a LOT of DB writes. My schema has, in addition to the primary key, another attribute with a unique constraint enforced.
Say I have an ID, an unique_constraint_field, and some other fields. I need to now update a row in the DB corresponding to the unique ID, given that no other row should already have the value of the unique_constraint_field value I am going to update.
Due to the high volume of updates (each update will affect only 1 row) I need to perform, (requiring low latency too) I rely on the primary key and the unique constraint on that attribute to catch a duplication, instead of an update statement using a subquery. This allows me to perform the update in a single query (which happens 95% of the time), and in the remaining 5%, I can catch the exception to take necessary action about the primary key or unique attribute violation.
I am currently using the ODBC mysql driver. However the driver returns a very generic error message for ANY error. Although right now my prototype is working well when I assume any error to be a key violation, this model obviously is pretty much flawed. I cannot find any other decent driver/way to connect to mysql from erlang.
I am thinking of switching to Mnesia (memory-only mode for my speed requirements) as Erlang and Mnesia meld so seamlessly. However, I see that Mnesia does not have any unique key constraints which I can use to perform my DB update in a single query.
I need suggestions as how best to implement this requirement from within Erlang. Is there any way of performing a conditional update in Mnesia? Or, is there any other high speed DB alternative I should be looking at? Any help/insight is greatly appreciated.
I don't know what is best solution but what I'd try is make two tables one for records and one for index with unique_constraint_field and handle each CUD from CRUD operations in transaction which check and update index. Reason is that in mnesia you can't set index type and always is duplicate bag. I think that because your index will be unique anyway, it should not introduce any additional performance penalty. If you use mnesia index feature you would still have to write your own CUD operations and result seems almost same as using two tables. Fortunately mnesia handles nested transaction with minimal developer effort and this thing is relatively easy compared to classical RDBMSs.