[Maria-developers] Help with running a transaction inside the server
Hi Serg, Can you give me a tip for doing the following (for MDEV-26, global transaction ID): I need to run a transaction inside the server. This is in the slave I/O thread, when it connects to the master it receives the current replication position as a GTID, and I want to store it in the mysql.rpl_slave_state so that next reconnect of the slave can use the GTID automatically. So I basically need to run the equivalent of something like this: BEGIN; INSERT INTO mysql.rpl_slave_state VALUES (...); DELETE FROM mysql.rpl_slave_state WHERE id=...; COMMIT; I think I already have everything except the "BEGIN" part (if you want, you can check the code in rpl_slave_state::record_gtid() in my branch lp:~maria-captains/maria/10.0-mdev26/ on Launchpad). So how do I do the equivalent of BEGIN inside the server code? Can you think of any existing code I could use as example? - Kristian.
Hi, Kristian! On Feb 13, Kristian Nielsen wrote:
Hi Serg,
Can you give me a tip for doing the following (for MDEV-26, global transaction ID):
I need to run a transaction inside the server. This is in the slave I/O thread, when it connects to the master it receives the current replication position as a GTID, and I want to store it in the mysql.rpl_slave_state so that next reconnect of the slave can use the GTID automatically.
So I basically need to run the equivalent of something like this:
BEGIN; INSERT INTO mysql.rpl_slave_state VALUES (...); DELETE FROM mysql.rpl_slave_state WHERE id=...;
You could do all that in a single statement, and you won't need a multi-statement transaction.
COMMIT;
I think I already have everything except the "BEGIN" part (if you want, you can check the code in rpl_slave_state::record_gtid() in my branch lp:~maria-captains/maria/10.0-mdev26/ on Launchpad).
What file is it in? I didn't find it in sql_repl.cc
So how do I do the equivalent of BEGIN inside the server code? Can you think of any existing code I could use as example?
See case SQLCOM_BEGIN in sql_parse.cc. You'll see that all what it does is one function call: trans_begin() But see above - I don't understand why would you need an explicit multi-statement transaction. Regards, Sergei
Sergei Golubchik <serg@askmonty.org> writes:
What file is it in? I didn't find it in sql_repl.cc
Sorry, it is rpl_slave_state::record_gtid() in sql/log_event.cc: http://bazaar.launchpad.net/~maria-captains/maria/10.0-mdev26/view/head:/sql...
You could do all that in a single statement, and you won't need a multi-statement transaction.
Right. In fact, I think perhaps I am doing it in a single statement already: mysql_reset_thd_for_next_command(thd, 0); open_and_lock_tables(thd, &tlist, FALSE, 0); table->file->ha_write_row(table->record[0]); table->file->ha_rnd_pos_by_record(table->record[0])); table->file->ha_delete_row(table->record[0]); ha_commit_trans(thd, FALSE); close_thread_tables(thd); if (!in_transaction) ha_commit_trans(thd, TRUE); (The in_transaction flag is true when the code runs as part of a slave replicated transaction. It is false when there is no containing transaction, which is the case I am trying to get to work now). Maybe that is all there is to it? I just thought I would need to somehow specify the start of the "transaction", even if it is just a single statement. Or maybe I need to set autocommit? I am always a little unsure about the logic around the all=TRUE_FALSE flag for commit and autocommit and how that works...
But see above - I don't understand why would you need an explicit multi-statement transaction.
Right, sorry for not explaining my problem very well. I suppose I just need some general advice on how to do DML operations from within the server. What needs to be set up in the THD, what needs to be done at the start of transaction or autocommit statement, and what needs to be done at the end ... Thanks! - Kristian.
Hi, Kristian! On Feb 14, Kristian Nielsen wrote:
Sergei Golubchik <serg@askmonty.org> writes:
What file is it in? I didn't find it in sql_repl.cc
Sorry, it is rpl_slave_state::record_gtid() in sql/log_event.cc:
http://bazaar.launchpad.net/~maria-captains/maria/10.0-mdev26/view/head:/sql...
Thanks. I tried to look at the source in launchpad, without branching, and online sources are a bit difficult to grep :)
You could do all that in a single statement, and you won't need a multi-statement transaction.
Right. In fact, I think perhaps I am doing it in a single statement already:
mysql_reset_thd_for_next_command(thd, 0); open_and_lock_tables(thd, &tlist, FALSE, 0); table->file->ha_write_row(table->record[0]); table->file->ha_rnd_pos_by_record(table->record[0])); table->file->ha_delete_row(table->record[0]); ha_commit_trans(thd, FALSE); close_thread_tables(thd); if (!in_transaction) ha_commit_trans(thd, TRUE);
(The in_transaction flag is true when the code runs as part of a slave replicated transaction. It is false when there is no containing transaction, which is the case I am trying to get to work now).
Maybe that is all there is to it? I just thought I would need to somehow specify the start of the "transaction", even if it is just a single statement. Or maybe I need to set autocommit? I am always a little unsure about the logic around the all=TRUE_FALSE flag for commit and autocommit and how that works...
Yes, that is all there is to it. A transaction starts automatically on the first "transaction-initiating statement" (according to the standard). You don't need to set autocommit. If you do - you won't need the second ha_commit_trans(thd, TRUE). You current code, as far as I understand, will work correctly, whether autocommit is on or off.
I suppose I just need some general advice on how to do DML operations from within the server. What needs to be set up in the THD, what needs to be done at the start of transaction or autocommit statement, and what needs to be done at the end ...
Just do it as above. In that case, though, your changes may be part of the already running transaction - do you want that? Oh, hmm, a problem. If you're in the middle of the already running transaction, you probably shouldn't commit it after your changes, but wait till the end - arbitrary adding commits in the middle of a transaction is no good. But perhaps you cannot be in the middle of a running transaction when you do record_gtid(). Regards, Sergei
Sergei Golubchik <serg@askmonty.org> writes:
Right. In fact, I think perhaps I am doing it in a single statement already:
mysql_reset_thd_for_next_command(thd, 0); open_and_lock_tables(thd, &tlist, FALSE, 0); table->file->ha_write_row(table->record[0]); table->file->ha_rnd_pos_by_record(table->record[0])); table->file->ha_delete_row(table->record[0]); ha_commit_trans(thd, FALSE); close_thread_tables(thd); if (!in_transaction) ha_commit_trans(thd, TRUE);
Yes, that is all there is to it. A transaction starts automatically on the first "transaction-initiating statement" (according to the standard).
You don't need to set autocommit. If you do - you won't need the second ha_commit_trans(thd, TRUE). You current code, as far as I understand, will work correctly, whether autocommit is on or off.
Aha, I see. Ok, thanks a lot for clarifying that!
In that case, though, your changes may be part of the already running transaction - do you want that?
Indeed, the whole point of this is to be part of the running transaction. So that slave state becomes crash-safe, unlike now where we first commit and then write to the file relay-log.info - and a crash in the middle makes us inconsistent, etc. So the intention with record_gtid() is to add an extra statement to the transaction being replicated - which updates the slave state. If we crash, we either recover or rollback the whole transaction, which includes both the replicated changes and the update of slave status. So we are crash safe (assuming transactional support in all involved engines, of course).
Oh, hmm, a problem. If you're in the middle of the already running transaction, you probably shouldn't commit it after your changes, but wait till the end - arbitrary adding commits in the middle of a transaction is no good.
So this is what I try to address with this code:
ha_commit_trans(thd, FALSE); close_thread_tables(thd); if (!in_transaction) ha_commit_trans(thd, TRUE);
My intension was - first commit the "statement" (all=FALSE). Then, if I am part of a larger transaction, do not commit the transaction, postpone that to later. But if not part of a transaction, I have to commit it here. But maybe I misunderstood? I thought a call of ha_commit_trans with all=FALSE was done/needed after every statement. Suppose we run this on the master: UPDATE t1 SET b=1 WHERE a=1; This gets written to binlog as two statement events and one XID event: Statement: "BEGIN" Statement: "UPDATE t1 SET b=1 WHERE a=1" Xid-event (meaning commit) So my thought was that the UPDATE does a ha_commit_trans(thd, FALSE). In the Xid-event I inject a call to record_gtid, which updates the mysql.rpl_slave_state table, and I therefore also need ha_commit_trans(thd, FALSE). Then finally Xid-event does ha_commit_trans(thd, TRUE). But maybe this is incorrect? I'm not sure ... maybe ha_commit_trans() is only called at the end of the transaction? - Kristian.
Hi, Kristian! On Feb 14, Kristian Nielsen wrote:
Sergei Golubchik <serg@askmonty.org> writes:
Right. In fact, I think perhaps I am doing it in a single statement already:
mysql_reset_thd_for_next_command(thd, 0); open_and_lock_tables(thd, &tlist, FALSE, 0); table->file->ha_write_row(table->record[0]); table->file->ha_rnd_pos_by_record(table->record[0])); table->file->ha_delete_row(table->record[0]); ha_commit_trans(thd, FALSE); close_thread_tables(thd); if (!in_transaction) ha_commit_trans(thd, TRUE);
Indeed, the whole point of this is to be part of the running transaction. So that slave state becomes crash-safe, unlike now where we first commit and then write to the file relay-log.info - and a crash in the middle makes us inconsistent, etc.
Okay, I see.
If you're in the middle of the already running transaction, you probably shouldn't commit it after your changes, but wait till the end - arbitrary adding commits in the middle of a transaction is no good.
So this is what I try to address with this code:
ha_commit_trans(thd, FALSE); close_thread_tables(thd); if (!in_transaction) ha_commit_trans(thd, TRUE);
My intension was - first commit the "statement" (all=FALSE). Then, if I am part of a larger transaction, do not commit the transaction, postpone that to later. But if not part of a transaction, I have to commit it here.
Ah, okay. I didn't check what "in_transaction" means in the rpl_slave_state. Regards, Sergei
Hi!
"Kristian" == Kristian Nielsen <knielsen@knielsen-hq.org> writes:
Kristian> Sergei Golubchik <serg@askmonty.org> writes:
What file is it in? I didn't find it in sql_repl.cc
Kristian> Sorry, it is rpl_slave_state::record_gtid() in sql/log_event.cc: Kristian> http://bazaar.launchpad.net/~maria-captains/maria/10.0-mdev26/view/head:/sql...
You could do all that in a single statement, and you won't need a multi-statement transaction.
Kristian> Right. In fact, I think perhaps I am doing it in a single statement already: Kristian> mysql_reset_thd_for_next_command(thd, 0); Kristian> open_and_lock_tables(thd, &tlist, FALSE, 0); Kristian> table->file->ha_write_row(table->record[0]); Kristian> table->file->ha_rnd_pos_by_record(table->record[0])); Kristian> table->file->ha_delete_row(table->record[0]); Kristian> ha_commit_trans(thd, FALSE); Kristian> close_thread_tables(thd); Kristian> if (!in_transaction) Kristian> ha_commit_trans(thd, TRUE); Why doing an insert + delete instead of one update? Regards, Monty
Michael Widenius <monty@askmonty.org> writes:
Why doing an insert + delete instead of one update?
This is described in more detail in my blog under "On crash-safe slave": http://kristiannielsen.livejournal.com/17008.html The basic problem with just one UPDATE is that this causes row lock contention when we get parallel replication, such as running group committed transactions in parallel, or the parallel replication patch of dingqi/Taobao. Suppose that we replicate transactions T1 and T2 in parallel. They both try to UPDATE the replication state. One will go first, say T1, and lock the row, causing T2 to have to wait. Only when T1 has committed will it release row locks, allowing T2 to do the update and later commit. So T1 and T2 can not commit in parallel. This makes group commit impossible, which is a huge performance hit in some workloads. Instead, the MDEV-26 design does a new insert for every change of the replication state. This avoids row locks, and allows group commit to work. Periodically we must then delete no longer needed rows, which can be done in batches, or every commit, or whatever. - Kristian.
participants (3)
-
Kristian Nielsen -
Michael Widenius -
Sergei Golubchik