With "INSERT IGNORE", the INSERT executes successfully even though there is a NOT NULL constraint on column c1.
In transaction, although the UPDATE executes unsuccessfully and reports an error, it locks the records and blocks other transaction.
/* init */ CREATE TABLE t(c1 BLOB NOT NULL, c2 TEXT);
|
/* init */ INSERT IGNORE INTO t VALUES (NULL, NULL), (NULL, 'aaa');
|
/* t1 */ BEGIN;
|
/* t1 */ UPDATE t SET c2='test' WHERE c1;
|
/* t1 */ ERROR 1292 (22007): Truncated incorrect DOUBLE value: ''
|
/* t2 */ BEGIN;
|
/* t2 */ UPDATE t SET c2 = 'def'; -- t2 is blocked
|
/* t1 */ COMMIT; -- t2 is unblocked
|
/* t2 */ COMMIT;
|