[ https://forge.continuent.org/jira/browse/SEQUOIA-1034?page=comments#action_14577 ]
Robert Hodges commented on SEQUOIA-1034: ---------------------------------------- Hi Emmanuel, Thanks for the review. Some comments. 1.) Yes, it's true that this approach would require all writes to be serialized. However, it does not required us to track transactions explicitly, and there is no explicit notion of conflicting or non-conflicting queue, which Stephane found to be a root cause of race conditions on setting auto-increment keys. It is interesting that we still get a dual queue effect when some requests are blocked in the database and some in middleware, so the race conditions are not fully eliminated. 2.) We definitely don't want to fetch lock status each statement. It should work more like existing deadlock detection. When we find the queue has not "moved" for some period of time, we note the current lock requests and scan the database looking for blocked sessions that hold those locks. If we find such sessions, we free the locks using some deterministic procedure so that locks would always be freed in the same order. 3.) DDL, yes absolutely we need to account for this. In this algorithm, they would be treated just like DML. Sequoia seems to be gathering pretty much exactly the information we need to make decisions with the possible exception of sub-selects. 4.) Multi-table queries & sprocs. I think these still work the same way--we just lock all tables for the duration of the request. Can you think of any examples that look really interesting? 5.) SELECTS. InnoDB is a bit confusing. Your comment made me nervous so I reread the manual. InnoDB uses MVCC by default for SELECT at the read committed level. SELECT statements see a consistent snapshot for the duration of the current transaction; they do not hold locks. However, we do need to broadcast requests like the following: SELECT * FROM parent WHERE age > 45 LOCK IN SHARE MODE; The commercial code does have additional parsing for this so statements are correctly broadcast. I have a final question--there are so many difficulties practical and otherwise with request determinism that it makes me suspect we may be dealing with one or more impossibility results. Do you know of any research on transactions and locking that would be relevant? It might be time to go back into Weikum and Vossen for a few days, though the last time I looked there was little joy. If there is helpful research it is probably still in individual articles. > Possible race condition leading to concurrent requests being executed in a > different order on backends > ------------------------------------------------------------------------------------------------------ > > Key: SEQUOIA-1034 > URL: https://forge.continuent.org/jira/browse/SEQUOIA-1034 > Project: Sequoia > Type: Bug > Components: Core > Versions: Sequoia 2.10.9 > Reporter: Stephane Giron > Assignee: Stephane Giron > Priority: Critical > Fix For: sequoia 2.10.11 > > > This can lead to backends inconsistencies. > As an example, this is the behavior that was observed : > CREATE TABLE `table1` ( > `db_id` bigint(20) NOT NULL auto_increment, > `col1` varchar(255) default NULL, > `creation_date` datetime default NULL, > `request_date` varchar(255) default NULL, > `col2` varchar(255) default NULL, > `version` varchar(255) default NULL, > PRIMARY KEY (`db_id`), > UNIQUE KEY `col1` (`col1`,`col2`) > ) ENGINE=InnoDB DEFAULT CHARSET=latin1; > Transaction 1 > begin > insert into table1 (version) values('a1'); > insert into table1 (version) values('c1'); > commit > Transaction 2 > begin > insert into table1 (version) values('b') > commit > T1 begin > T2 begin > T1 insert 1 > T2 insert 1 > T1 insert 2 > commits > The following result is what I got in the backends : > On one controller : > | 8 | NULL | NULL | NULL | NULL | a1 | > | 9 | NULL | NULL | NULL | NULL | c1 | > | 10 | NULL | NULL | NULL | NULL | b | > On the other : > | 8 | NULL | NULL | NULL | NULL | a1 | > | 9 | NULL | NULL | NULL | NULL | b | > | 10 | NULL | NULL | NULL | NULL | c1 | > This can happen if the statement "T2 insert 1", which will be posted in the > conflicting queue, can be processed on one controller (because there is > nothing else at this time in the non conflicting queue), whereas on the other > controller the "T1 insert 2" statement arrives in the non conflicting queue > before "T2 insert 1" starts executing. > This behavior can be observed essentially because of the auto incremented > key. However other type of statements may run out of order as well. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: https://forge.continuent.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira _______________________________________________ Sequoia mailing list [email protected] https://forge.continuent.org/mailman/listinfo/sequoia
