MySQL Bugs: #61556: RBR should use TL_WRITE_CONCURRENT

Bug #61556	RBR should use TL_WRITE_CONCURRENT_INSERT
Submitted:	17 Jun 2011 21:58	Modified:	20 Jul 2011 11:02
Reporter:	Andrew Hutchings	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.1,5.5	OS:	Any
Assigned to:	Jon Stephens	CPU Architecture:	Any

Description:
When replicating using SBR where possible MyISAM uses concurrent insert locks (as it would for insert statements).  It appears when using RBR that TL_WRITE is set at the table_map event whereas in certain situations TL_WRITE_CONCURRENT_INSERT will give better performance.

How to repeat:
.

What exact versions do you mean? I've check with recent mysql-5.5 from bzr (5.5.15), with STATEMENT fromat first and then ROW, and for both InnoDB and MyISAM in all cases I see the following in mysqladmin debug results when called while INSERT is processed (waiting for sleep(N)):

Thread database.table_name          Locked/Waiting        Lock_type

1       test.mi                     Locked - write        Concurrent insert lock

This is what I have in the binary log:

...
| macbook-pro-bin.000003 |  107 | Query       |         1 |         211 | use `test`; DROP TABLE `mi` /* generated by server */                  |
| macbook-pro-bin.000003 |  211 | Query       |         1 |         332 | use `test`; create table mi(c1 int auto_increment primary key, c2 int) |
| macbook-pro-bin.000003 |  332 | Query       |         1 |         400 | BEGIN                                                                  |
| macbook-pro-bin.000003 |  400 | Intvar      |         1 |         428 | INSERT_ID=1                                                            |
| macbook-pro-bin.000003 |  428 | Query       |         1 |         541 | use `test`; insert into mi(c2) values (sleep(20)), (sleep(10))         |
| macbook-pro-bin.000003 |  541 | Xid         |         1 |         568 | COMMIT /* xid=122 */                                                   |
| macbook-pro-bin.000003 |  568 | Query       |         1 |         636 | BEGIN                                                                  |
| macbook-pro-bin.000003 |  636 | Table_map   |         1 |         678 | table_id: 91 (test.mi)                                                 |
| macbook-pro-bin.000003 |  678 | Write_rows  |         1 |         725 | table_id: 91 flags: STMT_END_F                                         |
| macbook-pro-bin.000003 |  725 | Xid         |         1 |         752 | COMMIT /* xid=187 */                                                   |
| macbook-pro-bin.000003 |  752 | Query       |         1 |         843 | use `test`; alter table mi engine=MyISAM                               |
| macbook-pro-bin.000003 |  843 | Query       |         1 |         911 | BEGIN                                                                  |
| macbook-pro-bin.000003 |  911 | Table_map   |         1 |         953 | table_id: 94 (test.mi)                                                 |
| macbook-pro-bin.000003 |  953 | Write_rows  |         1 |        1000 | table_id: 94 flags: STMT_END_F                                         |
| macbook-pro-bin.000003 | 1000 | Query       |         1 |        1069 | COMMIT
...

Do I miss something in my test above?

In 5.5.12 I see:

Thread database.table_name          Locked/Waiting        Lock_type

3       test.t1                     Locked - write        High priority write lock

Table is:
CREATE TABLE `t1` (
  `a` int(11) NOT NULL,
  `b` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Inserts are of the form:

INSERT INTO t1 VALUES (1, '3D33mfzykzeCePemcM1P4dWG');

I shall try the bzr version later today and update this.

Also reproduced easily with today's 5.5 bzr:

Thread database.table_name          Locked/Waiting        Lock_type
1       test.t1                     Locked - write        High priority write lock

Also for added verification, this is the lock shown on the master:

Thread database.table_name          Locked/Waiting        Lock_type
2       test.t1                     Locked - write        Concurrent insert lock

Thank you for the report.

Verified as described using sandbox and test case provided by Valeriy:

1. Prepare table on master, set binlog_format=row
2. Lock table read on slave
3. On master run INSERT QUERY
4. In third terminal run `while true; do ./node1/my sqladmin debug; done`
5. After master finished executing, wiat 2 seconds, then unlock tables on slave and kill loop
6. Examine log with `cat node1/data/msandbox.err | grep test.mi | grep 4` where 4 is slave thread

I'm not sure if this is actually feasible. Since the lock applies only to INSERT (with append semantic), it would require knowing in advance the type of the subsequent events. Also, a write rows event is not a simple "insert", it might have replace/update/delete semantics if the record already exists in the table.

Hi Davi,

Totally agree, and since NDB->MyISAM is possible the read-ahead for those batches will not be fun to code.  When I opened the bug I didn't think that far into the resolution :)

The only other "fix" I thought of was to move the lock/unlock to the *_row event (and watching out for IDEMPOTENT).  But this would probably cause a performance hit as well as the possibility of breaking things like atomic multi-table writes.

I think the resolution could be some kind of documentation of this.

Agree. We can add a remark about this to the "Comparison of Statement-Based and Row-Based Replication" section as a disadvantage of row-based replication when used with MyISAM.

Something like: "For MyISAM: a stronger lock is required on the slave for a INSERT statement. MyISAM's concurrent insert is not supported when applying events."

Sounds great to me.  Many thanks for looking into it :)

Replication docs, should be mine.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Fixed by rev 26873.