Mnesia: How to lock multiple rows simultaneously so that I can write/read a "consistent" set of of records

Question

HOW I WISH I HAD PHRASED MY QUESTION TO BEGIN WITH

Take a table with 26 keys, a-z and let them have integer values. Create a process, Ouch, that does two things over and over again

1. In one transaction, write random values for a, b, and c such that those values always sum to 10
2. In another transaction, read the values for a, b and c and complain if their values do not sum to 10

If you spin-up even a few of these processes you will see that very quickly a, b and c are in a state where their values do not sum to 10. I believe there is no way to ask mnesia to "lock these 3 records before starting the writes (or reads)", one can only have mnesia lock the records as it gets to them (so to speak) which allows for the set of records' values to violate my "must sum to 10" constraint.

If I am right, solutions to this problem include

1. lock the entire table before writing (or reading) the set of 3 records -- I hate to lock whole table for 3 recs,
2. Create a process that keeps track of who is reading or writing which keys and protects bulk operations from anyone else writing or reading until the operation is completed. Of course I would have to make sure that all processes made use of this... crap, I guess this means writing my own AccessMod as the fourth parameter to activity/4 which seems like a non-trivial exercise
3. Some other thing that I am not smart enough to figure out.

thoughts?

Ok, I'm an ambitious Erlang newbee, so sorry if this is a dumb question, but

I am building an application-specific, in-memory distributed cache and I need to be able to write sets of Key, Value pairs in one transaction and also retrieve sets of values in one transaction. In other words I need to 1) Write 40 key,value pairs into the cache and ensure that no one else can read or write any of these 40 keys during this multi-key write operation; and, 2) Read 40 keys in one operation and get back 40 values knowing that all 40 values have been unchanged from the moment that this read operation started until it ended.

The only way I can think of doing this is to lock the entire table at the beginning of the fetch_keylist([ListOfKeys]) or at the beginning of the write_keylist([KeyValuePairs], but I don't want to do this because I have many processes simultaneously doing their own multi_key reads and writes and I don't want to lock the entire table any time any process needs to read/write a relatively small subset of records.

Help?

Trying to be more clear: I do not think this is just about using vanilla transactions

I think I am asking a more subtle question than this. Imagine that I have a process that, within a transaction, iterates through 10 records, locking them as it goes. Now imagine this process starts but before it iterates to the 3rd record ANOTHER process updates the 3rd record. This will be just fine as far as transactions go because the first process hadn't locked the 3rd record (yet) and the OTHER process modified it and released it before the first process got to it. What I want is to be guaranteed that once my first process starts that no other process can touch the 10 records until the first process is done with them.

PROBLEM SOLVED - I'M AN IDIOT... I guess... Thank you all for your patients, especially Hynek -Pichi- Vychodil! I prepared my test code to show the problem, and I could in fact reproduce the problem. I then simplified the code for readability and the problem went away. I was not able to again reproduce the problem. This is both embarrassing and mysterious to me since I had this problem for days. Also mnesia never complained that I was executing operations outside of a transaction and I have no dirty transactions anywhere in my code, I have no idea how I was able to introduce this bug into my code!

I have pounded the notion of Isolation into my head and will not doubt that it exists again.

Thanks for the education.

Actually, turns out the problem was using try/catch around mnesia operations within a transaction. See here for more.

Louisiana

A mnesia transaction should do this. Is there any reason that won't work for you? – Precocity

Because if I have a list of 10 keys that I am iterating through, the transactional system can lock the first record as I iterate to it, and then the second one, but someone can change the 10th key before this code gets to it. I need a way to, essentially, lock all of the records in the group that I am trying to change atomically. – Louisiana

Answer 1

Mnesia transaction will do exactly this thing for you. It is what is transaction for unless you do dirty operations. So just place your write and read operations to one transaction a mnesia will do rest. All operations in one transaction is done as one atomic operation. Mnesia transaction isolation level is what is sometimes known as "serializable" i.e. strongest isolation level.

Edit:

It seems you missed one important point about concurrent processes in Erlang. (To be fair it is not only true in Erlang but in any truly concurrent environment and when someone arguing else it is not really concurrent environment.) You can't distinguish which action happen first and which happen second unless you do some synchronization. Only way you can do this synchronization is using message passing. You have guaranteed only one thing about messages in Erlang, ordering of messages sent from one process to other process. It means when you send two messages M1 and M2 from process A to process B they arrives in same order. But if you send message M1 from A to B and message M2 from C to B they can arrive in any order. Simply because how you can even tell which message you sent first? It is even worse if you send message M1 from A to B and then M2 from A to C and when M2 arrives to C send M3 from C to B you don't have guarantied that M1 arrives to B before M3. Even it will happen in one VM in current implementation. But you can't rely on it because it is not guaranteed and can change even in next version of VM just due message passing implementation between different schedulers.

It illustrates problems of event ordering in concurrent processes. Now back to the mnesia transaction. Mnesia transaction have to be side effect free fun. It means there may not be any message sending outside from transaction. So you can't tell which transaction starts first and when starts. Only thing you can tell if transaction succeed and they order you can only determine by its effect. When you consider this your subtle clarification makes no sense. One transaction will read all keys in atomic operation even it is implemented as reading one key by one in transaction implementation and your write operation will be also performed as atomic operation. You can't tell if write to 4th key in second transaction was happen after you read 1st key in first transaction because there it is not observable from outside. Both transaction will be performed in particular order as separate atomic operation. From outside point of view all keys will be read in same point of time and it is work of mnesia to force it. If you send message from inside of transaction you violate mnesia transaction property and you can't be surprised it will behave strange. To be concrete, this message can be send many times.

Edit2:

If you spin-up even a few of these processes you will see that very quickly a, b and c are in a state where their values do not sum to 10.

I'm curious why you think it would happen or you tested it? Show me your test case and I will show mine:

-module(transactions).

-export([start/2, sum/0, write/0]).

start(W, R) ->
  mnesia:start(),
  {atomic, ok} = mnesia:create_table(test, [{ram_copies,[node()]}]),
  F = fun() ->
      ok = mnesia:write({test, a, 10}),
      [ ok = mnesia:write({test, X, 0}) || X <-
        [b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z]],
      ok
  end,
  {atomic, ok} = mnesia:transaction(F),
  F2 = fun() ->
    S = self(),
    erlang:send_after(1000, S, show),
    [ spawn_link(fun() -> writer(S) end) || _ <- lists:seq(1,W) ],
    [ spawn_link(fun() -> reader(S) end) || _ <- lists:seq(1,R) ],
    collect(0,0)
  end,
  spawn(F2).

collect(R, W) ->
  receive
    read -> collect(R+1, W);
    write -> collect(R, W+1);
    show ->
      erlang:send_after(1000, self(), show),
      io:format("R: ~p, W: ~p~n", [R,W]),
      collect(R, W)
  end.

keys() ->
  element(random:uniform(6),
    {[a,b,c],[a,c,b],[b,a,c],[b,c,a],[c,a,b],[c,b,a]}).

sum() ->
  F = fun() ->
      lists:sum([X || K<-keys(), {test, _, X} <- mnesia:read(test, K)])
  end,
  {atomic, S} = mnesia:transaction(F),
  S.

write() ->
  F = fun() ->
      [A, B ] = L = [ random:uniform(10) || _ <- [1,2] ],
      [ok = mnesia:write({test, K, V}) || {K, V} <- lists:zip(keys(),
          [10-A-B|L])],
      ok
  end,
  {atomic, ok} = mnesia:transaction(F),
  ok.

reader(P) ->
  case sum() of
    10 ->
      P ! read,
      reader(P);
    _ ->
      io:format("ERROR!!!~n",[]),
      exit(error)
  end.

writer(P) ->
  ok = write(),
  P ! write,
  writer(P).

If it would not work it would be really serious problem. There are serious applications including payment systems which rely on it. If you have test case which shows it is broken, please report bug at [email protected]

Viol

I think I am asking a more subtle question than this. I may be dense, but see my modified description above. – Louisiana

Thanks for the reply. Yes, I understand that. but, I can lock the table at the beginning of the process that updates the set of records. That works, but it is "costly" to do that. Some databases have a "Select with lock" command that does what I am looking for. I suppose that I could add an owner field to my record and write a function that iterates through each record in the set and tries to mark it with a unique ref and abort the transaction if it gets to a record owned by some other proces and continually retry this until it can mark them all before it updates. I think that would work, – Louisiana

Look at mine answer to shino. Once you read record inside transaction the record is locked with read lock. Then anybody can read this record but nobody can change. All select operations in mnesia transaction are with lock except dirty operations. – Viol

Serializable level or Repeatable read level? Does mneisa have the range lock? en.wikipedia.org/wiki/Isolation_(database_systems)#Serializable – Retarder

It seems that Mnesia will hold the table lock when do a range search, according to erlang.org/doc/man/mnesia.html#index_read-3 . So I think it is Searializable Level. – Retarder

Answer 2

Have you tried mnesia Events ? You can have the reader subscribe to mnesia's Table Events especially write events so as not to interrupt the process doing the writing. In this way, mnesia just keeps sending a copy of what has been written in real-time to the other process which checks what the values are at any one time. take a look at this:

subscriber()->
    mnesia:subscribe({table,YOUR_TABLE_NAME,simple}),
    %% OR mnesia:subscribe({table,YOUR_TABLE_NAME,detailed}),
    wait_events().

wait_events()->
receive
     %% For simple events
    {mnesia_table_event,{write, NewRecord, ActivityId}} -> 
        %% Analyse the written record as you wish
        wait_events();
     %% For detailed events
    {mnesia_table_event,{write, YOUR_TABLE, NewRecord, [OldRecords], ActivityId}} ->
        %% Analyse the written record as you wish
        wait_events();
    _Any -> wait_events()
end.

Now you spawn your analyser as a process like this:

spawn(?MODULE,subscriber,[]).

This makes the whole process to run without any process being blocked, mnesia needs not lock any tabel or record because now what you have is a writer process and an analyser process. The whole thing will run in real-time. Remember that there are many other events that you can make use of if you wish by pattern matching them in the subscriber wait_events() receive body.
Its possible to build a heavy duty gen_server or complete application intended for reception and analysis of all your mnesia events. Its usually better to have one capable subscriber than many failing event subscribers. If i have understood you question well, this unblocking solution fits your requirements.

Olva

Answer 3

mnesia:read/3 with write locks seems to be suffient.

Mnesia's transaction is implemented by read-write lock and locks are well-formed (holding lock untill the end of transaction). So the isolation level is serializable.

The granularity of locks are per record as long as you access by primary key.

Audiophile

Obtaining write lock doesn't have any sense here. Once you read some record you obtain read lock of this record. When you keep read lock nobody can write this this record which is sufficient to prevent any data change. Unless you are willing to write to this record obtaining write lock is pure waste of resources. – Viol

Thanks to pointing out my mistake. I think about write-skew (c.f. wiki.postgresql.org/wiki/SSI#Simple_Write_Skew), which affect some invariance e.g. sum of some records. But this has nothing with read-write lock transaction implementation. – Audiophile