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