Do Insert Operations Also Cause Write Conflicts? Why Are There Many Insert Write Conflicts in TiDB Blog?

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 写冲突insert也会写冲突吗,为什么tidblog里面很多insert写冲突

| username: 舞动梦灵

I found a table where all the data are insert operations, and all of them have primary key conflicts. I asked the developers, and they said the concurrency is between 20-70. I checked the data for a certain second and found 70 entries. The primary key data is a randomly generated UUID value at the code level. Why is there a primary key conflict, and I checked the conflicting index_values for the primary key, which can be queried.

| username: zhanggame1 | Original post link

This has nothing to do with the database, consider using an auto-increment or auto_random primary key.

| username: 舞动梦灵 | Original post link

This primary key is not auto-incremented, does it have anything to do with auto-increment? The primary key, along with all other fields, is inserted as concatenated insert values by the developers. The database was upgraded from version 2.0 to 4.0.2. The pessimistic lock value is empty. Not sure if it has anything to do with the pessimistic lock.

| username: tidb菜鸟一只 | Original post link

Could you share the table structure?

| username: forever | Original post link

Are there any errors reported in the application logs?

| username: dba远航 | Original post link

It feels like it’s caused by unreasonable concurrency on the development side.

| username: 舞动梦灵 | Original post link

I can’t view images directly. Please provide the text you need translated.

| username: 舞动梦灵 | Original post link

The application logs sometimes report errors and sometimes do not. He said there are retries on that side, and sometimes a single insert takes anywhere from 3 seconds to 23 seconds. I also saw an insert taking 20 seconds in the TiDB monitoring.

| username: tidb菜鸟一只 | Original post link

What I mean is the SQL statement for creating the table, show create table table_name.

| username: linnana | Original post link

Which transaction isolation level

| username: 舞动梦灵 | Original post link

CREATE TABLE `T_INSERT` (
  `ID` char(32) NOT NULL COMMENT 'ID',
  `DATA_TYPE` varchar(8) DEFAULT '' ,
  `SEND_STATE` char(1) DEFAULT '' ,
  `CHANNEL` char(4) DEFAULT '' ,
  `SEND_TIME` datetime DEFAULT CURRENT_TIMESTAMP ,
  `IN_BUSSINESS_ID` varchar(64) DEFAULT '' ,
  `OUT_BUSSINESS_ID` varchar(64) DEFAULT '' ,
  `SEND_DATA` text DEFAULT NULL COMMENT ,
  `PM_PARK_ID` varchar(32) COLLATE utf8_general_ci DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `INX_INBUSSINESSID` (`IN_BUSSINESS_ID`),
  KEY `INX_ISR_SENDTIME` (`SEND_TIME`),
  KEY `IND_UNION` (`PM_PARK_ID`,`SEND_TIME`,`CHANNEL`,`DATA_TYPE`,`SEND_STATE`,`OUT_BUSSINESS_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
| username: 舞动梦灵 | Original post link

Both are in RR mode (REPEATABLE-READ). The default for MySQL and TiDB is RR, unless manually adjusted.

| username: linnana | Original post link

What is the current value of the parameter for the transaction retry mechanism tidb_disable_txn_auto_retry?

| username: linnana | Original post link

The image is not visible. Please provide the text you need translated.

| username: zhanggame1 | Original post link

Take a look at the inserted logs, isn’t it possible that the primary key of the original SQL is conflicting?

| username: 舞动梦灵 | Original post link

At present, I can only think of this or the possibility that the database uses optimistic locking. The original SQL primary key is randomly generated UUID. It’s impossible to have a dozen identical 32-bit primary key values generated every day, right?

| username: 舞动梦灵 | Original post link

The value of auto is 1, and the limit count is 10.

| username: 路在何chu | Original post link

The primary key hasn’t been shuffled, right? Check if there are a lot of hotspots.

| username: zhanggame1 | Original post link

Check the application logs to see if there are duplicates. Also, what kind of UUIDs are you using? Do you have any examples? Ideally, they shouldn’t be duplicated. TiDB also doesn’t recommend storing UUIDs as CHAR. You can refer to this:

| username: 舞动梦灵 | Original post link

You see the value behind indexvalues in the topmost picture is the actual primary key value.