Deadlocks happened when massive inserting executed simultaneously in mysql 5.0

Question

I am using mysql 5.0 and I met some mysql deadlock problems when there were lots of inserts from different session at the same time (We estimated there would be a maximum about 900 insert statements executed in one second).

Here is the error I got:

1213, Deadlock found when trying to get lock; try restarting transaction

Here is one of my failure insert statement:

INSERT `cj_202203qmoh_prize_log` (`user_id`, `lottery_id`, `create_ip`, `flags`, `created_at`, `create_mac`)VALUES('388','58','???.???.???.???','0','2022-04-01 20:00:33','444937f4bc5d5aa8f4af3d96d31dbf61');

My table definition:

CREATE TABLE `cj_202203qmoh_prize_log` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `user_id` int(10) unsigned NOT NULL,
  `lottery_id` int(10) unsigned default NULL,
  `code` int(11) default NULL,
  `flags` int(10) unsigned default '0',
  `create_ip` varchar(64) NOT NULL,
  `create_mac` varchar(255) character set ascii NOT NULL,
  `created_at` timestamp NOT NULL default '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  USING BTREE (`id`),
  KEY `user_id` USING BTREE (`user_id`,`created_at`),
  KEY `user_id_2` USING BTREE (`user_id`,`lottery_id`),
  KEY `create_ip` USING BTREE (`create_ip`),
  KEY `create_mac` USING BTREE (`create_mac`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

I didn't use transactions at all in my business. Except for the insert statement, the only possible (in fact it's extremely improbable to be executed simultaneously) sql that requires a x-lock executes at the same time is:

UPDATE `cj_202203qmoh_prize_log` SET `code` = ? WHERE `id` = ?;

There are several select statements using index 'user_id' or 'user_id_2' could be executed simultaneously, but they don't need a s-lock.

And same user_id could only be inserted in the same session.

According to my company's policy, I have no privileges to run SHOW ENGINE INNODB STATUS, so I am afraid I could not provide further information.

---

After I set the transaction level to READ COMMITTED, execute the statement in a transaction and drop both create_ip and create_mac indexes, it seemed this problem have not happened again. But I still couldn't figure out what caused the deadlock.