Abandon the auto-increment ID of the database and expose the solution I used-Final

The purpose of writing this essay is to clarify some of the facts and opinions in my previous article "abandon the auto-increment ID of the database and expose the solution I used". At the same time, I will continue to answer the concurrent questions,

I will use the lock on the original stored procedure to solve the problem and attach my test code and test data.

The reason why I put it on the homepage does not mean that I have a lot of skills and expertise in this article. I just want to share a little bit of design ideas and project experience hidden in a programmer's mind. Improve individuals by sharing!

 

PS: the original intention of my writing is to share my technical knowledge, design ideas for problems, and share them to get better suggestions, it is not and may not be suitable for all or all scenarios. Even if it comes to manipulism, it must first go to its dregs and then take its essence! The original intention of some articles written by Lao Zhao has been misunderstood by many people, and it is really sad that some people do not understand the original intention of the author to launch attacks! But fortunately, Lao Zhao is solid enough to withstand the pressure ....

 

At the same time, I also share my views on some comments:

Many people say that using GUID instead is of course a method. If I say that the method to be exposed is actually using GUID to solve the problem of data value shifting and data segmentation, I believe there will be a lot of bricks to throw!

Some people say that using auto-increment ID can also set the start value. That's right. You need to use my method as I mentioned in my essay! However, I always think it is inconvenient to insert the associated data. I always need to get the key foreign key value first, but someone Peter. zhu said, you can get this value before inserting it. I really don't know this. I hope you can tell me ...,

Some people say that (alive Lei Feng Dorian Deng) it is ridiculous to discard auto-increment IDs for data transplantation and other situations every time... I have never figured out the migration frequency or probability. If this is a problem ten years later... I think this is a bit absolute. It is a bad thing to plan ahead. from a global perspective, the problem is absolutely true. There is no need to reject all the project demands together with your project experience. It depends on the type of project. There is no absolute thing in the world, and I have met it!

Most people talk about concurrency. Yes, there are indeed concurrency problems. I am very clear that this is one of its shortcomings. However, some people still use it to write articles, which is perfect, but for some small applications, it should be enough! Of course I used it too!

Finally, I would like to thank many people, such as llzhzhbb, cartoon, sinxsoft, Zhou Qiang ,.........

 

No more nonsense! Back to business

 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Separator ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~

 

1: Test prerequisites:

Before the test, I made a word change to the original Stored Procedure (of course, my stored procedure can be optimized, and some suggestions have been given in the previous article ), here I will not be so arrogant. Let's take a look at the stored procedure after I change it:

 

create procedure up_get_table_key(   @table_name     varchar(50),   @key_value      int output)asbegin     begin tran         declare @key  int                  --initialize the key with 1         set @key=1         --whether the specified table is exist         if not exists(select table_name from table_key where table_name=@table_name)            begin              insert into table_key values(@table_name,@key)        --default key vlaue:1            end         -- step increase         else                begin                select @key=key_value from table_key with (updlock) where table_name=@table_name                set @key=@key+1                --update the key value by table name                update table_key set key_value=@key where table_name=@table_name            end        --set ouput value    set @key_value=@key    --commit tran    commit tran        if @@error>0      rollback tranend

 

Note that I used updlock during the query. I locked the row during the search. The advantage of updlock is that it allows users to read data (without blocking other transactions) and update data later, make sure that the data has not been changed since the last read. That is to say, after I get the key value in this transaction call, the lock can ensure that the key value will not be changed, and sessions that do not actually get the same key value will not.

 

2: Test

My test environment: SQL server 2008,. net framework 3.5

First, attach my program code:

 

private void GetIncreateID()        {            while (!isStop)            {                int a = Database.GetIncreaseID("stud");                //int b = Database.GetIncreaseID("stud");                //int c = Database.GetIncreaseID("stud");                //int d = Database.GetIncreaseID("stud");                Database.AddStudent(a,a.ToString());                //Debug.WriteLine(string.Format("a={0},b={1},c={2},d={3}", a, b, c, d));                Thread.Sleep(200);            }        }        protected void start_Click(object sender, EventArgs e)        {            Thread thread1 = new Thread(new ThreadStart(GetIncreateID));            Thread thread2 = new Thread(new ThreadStart(GetIncreateID));            Thread thread3 = new Thread(new ThreadStart(GetIncreateID));            Thread thread4 = new Thread(new ThreadStart(GetIncreateID));            thread1.Start();            thread2.Start();            thread3.Start();            thread4.Start();            for(int i = 0;i<1000;i++)            {                Thread thread = new Thread(new ThreadStart(GetIncreateID));                thread.Start();            }        }        protected void stop_Click(object sender, EventArgs e)        {            isStop = true;        }

 

Data access layer code:

public static int GetIncreaseID(string tableName)        {            DbCommand command = CreateCommand();            command.CommandType = System.Data.CommandType.StoredProcedure;            command.CommandText = "up_get_table_key";   //GetSequence            DbParameter para1 = command.CreateParameter();            para1.ParameterName = "@table_name";            para1.Value = tableName;            para1.DbType = System.Data.DbType.String;            para1.Size = 50;            DbParameter para2 = command.CreateParameter();            para2.ParameterName = "@key_value";            para2.DbType = System.Data.DbType.Int32;            para2.Direction = System.Data.ParameterDirection.Output;            command.Parameters.Add(para1);            command.Parameters.Add(para2);            int increaseId = 0;            try            {                command.Connection.Open();                command.ExecuteNonQuery();                increaseId = Convert.ToInt32(command.Parameters["@key_value"].Value);             }            catch (DbException ex)            {                throw ex;            }            finally            {                command.Connection.Close();            }            return increaseId;        }        public static void AddStudent(int id, string name)        {            DbCommand command = CreateCommand();            command.CommandType = System.Data.CommandType.Text;            command.CommandText = string.Format("insert into stud values({0},'{1}')",id,name);            try            {                command.Connection.Open();                command.ExecuteNonQuery();            }            catch (DbException ex)            {                throw ex;            }            finally            {                command.Connection.Close();            }        }

 

I have enabled at least 1000 threads to insert data and insert data id and name, where id is the primary key value of the stud table. If the same ID value exists, an exception is thrown when data is inserted!

 

Let's look at my results again:

 

I have inserted more than 7 million pieces of data, which is purely for testing concurrency and does not have the same ID!

 

 

BTW, according to llzhzhbb's suggestion, I also tried to implement it to test concurrency! This is a brand new approach. Let's take a look at the ideas:

1. Define static int variables to replace the auto-increment ID

2: When the application starts, the static variable is initialized as the max id value of the table.

3: when inserting data, the static variable Interlocked. Increment is first increased by 1, and then new data is inserted.

This approach is actually to bypass the database and use applications to solve the concurrency, Interlocked. increment increments by generating atomic operations and stores results. It ensures the synchronization of multi-thread concurrency and makes the uniqueness of auto-Increment IDs.

If you do not use Interlocked. increment has the same concurrency problem because Auto-Increment operations are not an atomic operation on a large part of computers, in the incremental process, the CPU will first read the variable value you want to increment into the register and then perform auto-increment operations on it, at last, the auto-increment value in the register is saved to the variable. Obviously, three operations are performed in the middle. Therefore, it is impossible to guarantee the concurrency in multiple threads.

 

Of course, this is just a way of thinking. Let's take a look at its actual application. Let's talk about it with data. Let's first look at the Code:

 

public static class TableMappingVariable    {        public static int CourseMappingID = 0;        static TableMappingVariable()        {            DbCommand command = Database.CreateCommand();            command.CommandType = System.Data.CommandType.Text;            command.CommandText = "select max(cour_id) from course";            try            {                command.Connection.Open();                DbDataReader reader = command.ExecuteReader();                if (reader.Read())                {                    CourseMappingID = reader.GetInt32(0);                }            }            catch (DbException ex)            {                throw ex;            }            finally            {                command.Connection.Close();            }        }    }

 

For the sake of simplicity, I wrote it directly in the static constructor and took the maximum ID value during initialization.

Similarly, I have enabled at least 1000 threads in the test program to insert data.

 

protected void btnStart_Click(object sender, EventArgs e)        {            Thread thread1 = new Thread(new ThreadStart(GetIncreateID));            Thread thread2 = new Thread(new ThreadStart(GetIncreateID));            Thread thread3 = new Thread(new ThreadStart(GetIncreateID));            Thread thread4 = new Thread(new ThreadStart(GetIncreateID));            thread1.Start();            thread2.Start();            thread3.Start();            thread4.Start();            for (int i = 0; i < 1000; i++)            {                Thread thread = new Thread(new ThreadStart(AddCourse));                thread.Start();            }        }        public void AddCourse()        {            while (!isStop)            {                Database.AddCourse(Interlocked.Increment(ref TableMappingVariable.CourseMappingID), TableMappingVariable.CourseMappingID.ToString());                Thread.Sleep(200);            }        }        protected void btnStop_Click(object sender, EventArgs e)        {            isCourseStop = true;        }

 

Let's look at the test data:

 

 

I inserted more than 4 million of the data. The test results are correct! Compared with MAX (ID) + 1, this method does reduce the number of database accesses, MAX (ID) + 1 you need to search the table for MAX (ID) each time you insert the data ). in this way, I only need to read it once! Of course, this does not include the program failure ........

 

In summary, I don't want to talk too much about it. Of course, you can continue to use GUID, auto-increment ID, or MAX (ID) + 1 ..... and so on as the primary key of your database, but these are not important to me. I don't know about golden primary K3, SQLLITE, and I don't know much about ORACLE, but I don't know what copmiere is, I don't even know their design ideas. If they can be made public, it would be better. This is purely for testing concurrency! Maybe not for you!

Welcome to comments!

 

Finally, add my test code: (click to download)

 

PS: By the way, I recommend my blog music! Really good! Haha...