MySQL Bugs: #35631: mgmapi reports abort on a unique index mix

Bug #35631	mgmapi reports abort on a unique index mix
Submitted:	28 Mar 2008 7:55	Modified:	4 Nov 2008 23:16
Reporter:	Monty Taylor	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Cluster: NDB API	Severity:	S3 (Non-critical)
Version:	mysql-5.1-telco-6.3	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	6.3.17
Triage:	Triaged: D3 (Medium)

Description:
When you do a unique index lookup on a value that is not in the unique index, this is reported by the MGM API in the TransReportCounters as an abort. This may be confusing to someone reading the TransReportCounters for operational statistics information. 

How to repeat:
Make a simple MGM API program that prints out TransReportCounters, or get get_ndb_status from http://edge.launchpad.net/get-ndb-status/trunk/1.3/+download/ndb_tools-1.3.tar.gz (compile it and then run get_ndb_status localhost foo & ; watch cat foo ) 

While it's running, do this: 

create table t1 ( x int primary key, y int not null, unique key (y) using hash) engine=ndbcluster;

insert into t1 values (1,1),(2,2),(3,3),(4,4),(5,5);

select * from t1 where y=10;

And you should see an abort reported. 

Suggested fix:
Don't count unique index misses as transaction aborts.

Verified using Cluster 6.3.17, current ndb-tools using bzr and:

sture% ./get_ndb_status -v -c localhost -f foo
2 trans:0 read:0 write:0 commit:0 abort:0 range:0 scan:0 pk:0 
2 trans:1 read:1 write:0 commit:0 abort:1 range:0 scan:0 pk:0 
2 trans:0 read:0 write:0 commit:0 abort:0 range:0 scan:0 pk:0 
2 trans:1 read:1 write:0 commit:0 abort:1 range:0 scan:0 pk:0 
2 trans:0 read:0 write:0 commit:0 abort:0 range:0 scan:0 pk:0 

## run the select twice, first after the first line showed up and
## second time after the third line was output.
                             
sture% cat foo
Trans_count:2 Read_count:2 Commit_count:0 Scan_count:0 Simple_read_count:0 Write_count:0 Attrinfo_count:2 Conc_op_count:0 Abort_count:2 Range_scan_count:0 Operations:2 TotalDataMemoryUsed:262144 TotalDataMemoryTotal:83886080 TotalDataPagesUsed:8 TotalDataPagesTotal:2560 TotalDataBlocks:249 TotalIndexMemoryUsed:114688 TotalIndexMemoryTotal:19136512 TotalIndexPagesUsed:14 TotalIndexPagesTotal:2336 TotalIndexBlocks:248 DataMemoryUsed2:262144 DataMemoryTotal2:83886080 DataPagesUsed2:8 DataPagesTotal2:2560 DataBlocks2:249 IndexMemoryUsed2:114688 IndexMemoryTotal2:19136512 IndexPagesUsed2:14 IndexPagesTotal2:2336 IndexBlocks2:248 

So two aborted transactions are reported in this case.

What appears to be happening is that the code in ha_ndbcluster::external_lock() which unlocks the 'external lock' is calling close_transaction().  As the transaction has 'state' in the kernel this results in the transaction being rolled back.

For a normal, autocommited PK read (e.g. select * from t1 where x=10;), a 'Simple Read' is used which leaves no kernel state and does not need a transaction rollback.

If a PK read with a lock is used (e.g. select * from t1 where x=10 for update;), the PK read does not use a simple read, and when the statement ends, the transaction is rolled back.

Perhaps ha_ndbcluster::external_lock() should be changed to commit the transaction instead of just closing it in these cases?