Hello Sergei, I understood most of the internals of long unique constraints in MyISAM. I am still going through the code in InnoDB. I will soon reply to you. Thanks, Shubham On 21 March 2016 at 16:37, Sergei Golubchik <serg@mariadb.org> wrote:
Hi, Shubham!
On Mar 21, Shubham Barai wrote:
I am currently looking into InnoDB codebase to see if it is possible for me to extend this feature to InnoDB storage engine. As InnoDB doesn't support this feature internally, it would require more time than MyISAM. Any suggestions regarding this would be helpful.
Heh, that's very good (and ambitious) :)
Do you already know how MyISAM supports arbitrary long UNIQUE constraints internally? It stores only the hash of the value (of the blob, for example) in the non-unique index, and on INSERT it checks if there are identical hashes in the index. If there are (hash collision) it will retrieve the rows and compare actual blob values.
It seems that InnoDB should use the same approach as MyISAM. It'll need some care for a case when two concurrent transactions insert conflicting rows - as transaction changes are not visible until commit, you won't see the conflict until it's too late. But gap locks [1] should be able to prevent that.
Regards, Sergei Chief Architect MariaDB and security@mariadb.org
[1] https://dev.mysql.com/doc/refman/5.7/en/innodb-record-level-locks.html