Erlang : Mnesia : Updating a single field value in a row

Question

I have an mnesia table with three fields, i, a and b, created using the record

-record(rec, {i, a,b}).

Now I insert a row into the table as:

mnesia:transaction( fun() -> mnesia:write("T", #rec{i=1, a=2, b=3}, write) end ).

Now what do I do if I want to update this row, and change only the value of a to 10, while leaving i and b with the same values? Is there any SQL equivalent like "UPDATE T SET a=10 WHERE i=1"?

If I do something like this:

mnesia:transaction( fun() -> mnesia:write("T", #rec{i=1, a=10}, write) end )

The row is stored as:

{rec,1,10,undefined}

Idiophone

Answer 1

The value of this function will update a if used in a mnesia:transaction

update_a(Tab, Key, Value) ->
  fun() ->
    [P] = mnesia:wread({Tab, Key}),
    mnesia:write(P#pixel{a=Value})
  end.

Suggestion: have a peek at QLC if you want some syntax sugar that is more like the SQL syntax.

The performance is of course best benchmarked, but QLC has overhead, I'm not sure they are relevant compared to the other details. I just figured that the SQL example you gave would update all records that have i=1. Using QLC to extract that set of records is prettier than mnesia calls.

Also to notice, wread claims a write lock on the record directly, because we know ahead of time that we will update that record. That's a micro-optimization to avoid first a read lock, then change our mind and get a write lock. I haven't benchmarked that in a long time though.

If performance is still an issue you should look at various approaches where you use dirty operations. But you really should try to figure out how many transactions per second you need, to be 'fast enough'.

Decani

I need to have an eye on efficiency, so would you recommend I use QLC or the method you've specified above? – Idiophone

I tried this, and for some reason it doesn't work. The value is not being updated. – Idiophone

@ErJab, check out dirty_update_counter, it might serve your needs. – Marseillaise

It works now, I was overseeing a little mistake with the table names in my code! – Idiophone

@Marseillaise The fields I want to update are not integers. The code I used in my question, was just an example. Thanks anyway, now I know I can use dirty_update_counter to update integer fields. – Idiophone

I'm not sure, but I think you missed the table name in mnesia:write. Isn't it update_a(Tab, Key, Value) -> fun() -> [P] = mnesia:wread({Tab, Key}), mnesia:write(Tab, P#rec{a=Value}, write) end. – Idiophone

Oh yeah, I'm used to being able to use erlang.org/doc/man/mnesia.html#write-1 which works when the record name and table name are the same. – Decani

Answer 2

I believe you need to read the "row", update whatever field you need, and then write back the result and all of these operations within a "transaction".

Mashie

Yeah I thought about that too, but wouldn't this be an expensive operation? – Idiophone

Database normally would do "UPDATE" operations in a transaction anyways or else how would they guarantee correctness? In mnesia I guess one needs to be more explicit, for some reason that escapes me at the moment. – Mashie