Several ways to avoid duplicate values from the number-picking function
Posted: Wed Jan 08, 2025 5:04 am
When I was developing the system a while ago, I accidentally discovered that the system would get the same number, causing the data in the DB to be messed up. After reading some information, I learned that any function related to taking numbers or serial numbers needs to be processed to avoid this. Here are several ways to deal with the situation of repeated number retrieval.
Table of contents
Lock statement
Transaction control (Transaction) + TABLOCKX syntax
SQL SERVER Sequence (sequence) or IDENTITY Column (identification field)
Transaction Control(Transaction) + UPDATE
Transaction control (Transaction) + WHERE condition verification
Reference:
Lock statement
The lock statement obtains the exclusive lock on the specified object, executes the statement block, and then releases the lock. This means that if a program runs into the Lock section and other programs want to access the program in this section, they will be required to wait for the program in the Lock section to finish running before they can enter.
usage:
First declare the object that provides Lock lock
Wrap the number-retrieval program to be executed with Lock, and the program code inside will be marked as a critical section . Other programs must wait for the number-retrieval action (critical section) to be completed before they can retrieve the number again. In this way Can solve the problem of repeated number retrieval.
shortcoming:
If there are multiple stations at the same time, the memory lock cannot accurately check the number, and other methods must be used in this case.
Transaction control (Transaction) + TABLOCKX syntax
Adding TABLOCKX syntax to SELECT will put this TABLE into the Exclusive Lock state, and this state will be maintained until the end of this Transaction. At this time, no other transaction actions can generate a Lock for this data table. Also, because of the use of Transaction, no one else can obtain the contents of this data table before the transaction is completed. In this way, when selecting the number (SELECT), it will be blocked. is to take the latest value.
shortcoming:
Because the TABLOCKX syntax will make other queries wait, if used too much or improperly, it will cause
Excessive waiting in the system reduces performance, so it still nigeria whatsapp phone number needs to be used according to demand.
SQL SERVER Sequence ( sequence) or IDENTITY Column ( identification field)
Both of these methods are set up in the database, allowing the database to control the serial number. Whenever there is new data, the serial number field will be automatically incremented by one to avoid duplication of single numbers.
IDENTITY Just add INT IDENTITY(1,1) to the serial number field when creating the Table, - starting from 1 and increasing by 1 each time. This field will automatically increase by one every time data is added.
The difference between SEQUENCE and Identity is that Sequence is not bound to a Table and can be shared in different Tables. The following two examples are Customers and Users; create a new Sequence Object and use NEXT VALUE for to add it, and you can see the two The serial numbers of the tables are calculated together.
Table of contents
Lock statement
Transaction control (Transaction) + TABLOCKX syntax
SQL SERVER Sequence (sequence) or IDENTITY Column (identification field)
Transaction Control(Transaction) + UPDATE
Transaction control (Transaction) + WHERE condition verification
Reference:
Lock statement
The lock statement obtains the exclusive lock on the specified object, executes the statement block, and then releases the lock. This means that if a program runs into the Lock section and other programs want to access the program in this section, they will be required to wait for the program in the Lock section to finish running before they can enter.
usage:
First declare the object that provides Lock lock
Wrap the number-retrieval program to be executed with Lock, and the program code inside will be marked as a critical section . Other programs must wait for the number-retrieval action (critical section) to be completed before they can retrieve the number again. In this way Can solve the problem of repeated number retrieval.
shortcoming:
If there are multiple stations at the same time, the memory lock cannot accurately check the number, and other methods must be used in this case.
Transaction control (Transaction) + TABLOCKX syntax
Adding TABLOCKX syntax to SELECT will put this TABLE into the Exclusive Lock state, and this state will be maintained until the end of this Transaction. At this time, no other transaction actions can generate a Lock for this data table. Also, because of the use of Transaction, no one else can obtain the contents of this data table before the transaction is completed. In this way, when selecting the number (SELECT), it will be blocked. is to take the latest value.
shortcoming:
Because the TABLOCKX syntax will make other queries wait, if used too much or improperly, it will cause
Excessive waiting in the system reduces performance, so it still nigeria whatsapp phone number needs to be used according to demand.
SQL SERVER Sequence ( sequence) or IDENTITY Column ( identification field)
Both of these methods are set up in the database, allowing the database to control the serial number. Whenever there is new data, the serial number field will be automatically incremented by one to avoid duplication of single numbers.
IDENTITY Just add INT IDENTITY(1,1) to the serial number field when creating the Table, - starting from 1 and increasing by 1 each time. This field will automatically increase by one every time data is added.
The difference between SEQUENCE and Identity is that Sequence is not bound to a Table and can be shared in different Tables. The following two examples are Customers and Users; create a new Sequence Object and use NEXT VALUE for to add it, and you can see the two The serial numbers of the tables are calculated together.