slepher/Emysql — comment on commit 50548682bb3eaa513a82883b40e326269476a6a4
File:  line 0
By Eonblast:

Interesting stuff slepher, how is it coming along?

Please take a look at this while you're at it. It's a Mnesia-style approach to transactions that Brendon Hogger started implementing a while ago. I believe they are running something very close to these lines at their company now but couldn't entice him to push.

%%%-------------------------------------------------------------------
%%% @author Brendon Hogger
%%% @copyright (C) 2011, Brendon Hogger
%%% @doc
%%% EMysql transaction wrapper
%%% @end
%%% Created : 31 Mar 2011 by Brendon Hogger
%%%-------------------------------------------------------------------
-module(bnb_transact).

-include("emysql.hrl").

-export([transact/2, transact/3,
test_transact/0]).

transact(PoolId, TransactFun) ->
transact(PoolId, TransactFun, infinity).

transact(PoolId, TransactFun, Timeout) when is_atom(PoolId) ->
Connection = emysql_conn_mgr:wait_for_connection(PoolId),
transact(Connection, TransactFun, Timeout);

transact(Connection, TransactFun, Timeout) ->
% This is almost straight from emysql:monitor_work/1

Parent = self(),
Pid = spawn(
fun() ->
receive start ->
Parent ! {self(), wrap(Connection, TransactFun)}
end
end),
Mref = erlang:monitor(process, Pid),
Pid ! start,
receive
{'DOWN', Mref, process, Pid, {_, closed}} ->
NewConnection = emysql_conn:renew_connection(emysql_conn_mgr:pools(), Connection),
transact(NewConnection, TransactFun, Timeout);
{'DOWN', Mref, process, Pid, Reason} ->
%% if the process dies, reset the connection
%% and re-throw the error on the current pid
emysql_conn:reset_connection(emysql_conn_mgr:pools(), Connection),
exit(Reason);
{Pid, Result} ->
%% if the process returns data, unlock the
%% connection and collect the normal 'DOWN'
%% message send from the child process
erlang:demonitor(Mref, [flush]),
emysql_conn_mgr:unlock_connection(Connection),
Result
after Timeout ->
%% if we timeout waiting for the process to return,
%% then reset the connection and throw a timeout error
erlang:demonitor(Mref),
exit(Pid, normal),
emysql_conn:reset_connection(emysql_conn_mgr:pools(), Connection),
exit(mysql_timeout)
end.

%% -------------------------------------------------------------

wrap(Connection, TransactFun) ->
emysql_conn:execute(Connection, <<"START TRANSACTION WITH CONSISTENT SNAPSHOT">>, []),
case catch(TransactFun(Connection)) of
{ok, _}=Result ->
emysql_conn:execute(Connection, <<"COMMIT">>, []),
Result;
{'EXIT', Reason} ->
emysql_conn:execute(Connection, <<"ROLLBACK">>, []),
{error, Reason};
OtherResult ->
emysql_conn:execute(Connection, <<"ROLLBACK">>, []),
OtherResult
end.

%% -------------------------------------------------------------

test_transact() ->
crypto:start(),
application:start(emysql),

ok = emysql:add_pool(test, 2, "brendonh", "arthur", "localhost", 3306, "bnb", utf8),

emysql:execute(test, <<"DROP TABLE IF EXISTS quicktest">>),
emysql:execute(test, <<"CREATE TABLE quicktest (val INTEGER) engine=innodb">>),
emysql:execute(test, <<"INSERT INTO quicktest VALUES (0)">>),

Tests = [
{success, fun(Conn) ->
emysql_conn:execute(
Conn, <<"UPDATE quicktest SET val = val + 1">>, []),
{ok, done}
end},

{crash, fun(Conn) ->
emysql_conn:execute(
Conn, <<"UPDATE quicktest SET val = val + 1">>, []),
1 / 0
end},

{error, fun(Conn) ->
emysql_conn:execute(
Conn, <<"UPDATE quicktest SET val = val + 1">>, []),
{error, whatever}
end}
],

lists:map(fun({Name, TestFun}) ->
io:format("-----------------------~n"),
io:format("Test: ~s~n", [Name]),
emysql:execute(test, <<"UPDATE quicktest SET val = 0">>),
TResult = transact(test, TestFun),
io:format("Result: ~p~n", [TResult]),
Result = emysql:execute(test, <<"SELECT val FROM quicktest">>),
io:format("Val after: ~p~n", [hd(hd(Result#result_packet.rows))])
end,
Tests).

%% $ erl -pa `agner prefix emysql`/ebin -pa ebin -noshell -s bnb_transact test_transact -s init stop
%% -----------------------
%% Test: success
%% Result: {ok,done}
%% Val after: 1
%% -----------------------
%% Test: crash
%% Result: {error,{badarith,[{bnb_transact,'-test_transact/0-fun-1-',1},
%% {bnb_transact,wrap,2},
%% {bnb_transact,'-transact/3-fun-0-',3}]}}
%% Val after: 0
%% -----------------------
%% Test: error
%% Result: {error,whatever}
%% Val after: 0