I’m trying to find a solution to a very common problem in databases - enforcing a primary key constraint. Problem is, the column I’m trying to enforce it on is more than 768 bytes in length, which is the maximum size of an index column in InnoDB. Lets say the data there is a URL, longer than 768 bytes.
This means I can’t use the native primary key enforcement that the database supplies, and have to develop something by myself.
I’ve tried different combinations of selects & inserts with different locking modes. The only way I could find to perform this is to lock the entire table:
SELECT url FROM t WHERE url=x;
then if we don’t find a row
LOCK TABLE t WRITE;SELECT url FROM t WHERE url=x;
If we don’t find a row again, we are certain it’s not there, and insert it.
INSERT INTO t VALUES (x)
Problem with this solution is the table lock. When the database performs regular locks to enforce primary keys, it lock records in place, and ranges in the index. Here, I have no B-Tree structure to do this, so I have to lock the entire table.
Can anyone think of any solution that avoids locking the entire table? It’s really horrible for performance and concurrency…
I’d appreciate any input! )