Same data is inserted during insert

Question

I have couple insert queries which are merged in transaction. First of that insert is to create new product articel number incrementing the most higher in table by one. Unfortunetly i just noticed that mostly during tests if for instance two users from two diffrent applications click button which trigger my transaction's method they could get same new product number. How can avoid that situation? Is there something like lock on first insertion so that if first user accessing table to insert restrict other's user/s about their insertion so they have to wait in queue after first user insert is finished? Is there something like that? Besides i thought if someone inserts other users are not able to insert. I made comments in code you to understand.

Part of my transaction query below:

Public Sub ProcessArticle(ByRef artikel As ArticlesVariations)
        Dim strcon = New AppSettingsReader().GetValue("ConnectionString", GetType(System.String)).ToString()
        Using connection As New SqlConnection(strcon)
            connection.Open()
            Using transaction = connection.BeginTransaction()
                Try

                    For Each kvp As KeyValuePair(Of Integer, Artikel) In artikel.collection
                        articleIndex = kvp.Key
                        Dim art As Artikel = kvp.Value

                            Using cmd As New SqlCommand("INSERT INTO tbArtikel (Nummer) VALUES (@Nummer);Select Scope_Identity()", transaction.Connection)
                                cmd.CommandType = CommandType.Text
                                cmd.Connection = connection
                                cmd.Transaction = transaction

                                 'Get next product number from table tbArtikel  (this will be new product number)'
                                 Dim NewArtNummer as String =  New DALArtikel().GetNewArtikelNumber(transaction)
                                 art.Nummer = NewArtNummer

                                cmd.Parameters.AddWithValue("@Nummer", art.Nummer)
'Get inserted product id for other diffrent inserts below'
 newArticleRowId = CInt(cmd.ExecuteScalar()) 

'....
other INSERTs queries to other tables ...
...'

       transaction.Commit()
                Catch ex As Exception
                    transaction.Rollback()
                    Throw 'Rethrow exception.'
                End Try
            End Using
        End Using
    End Sub

Answer 1

Just about the only way to assure that users are not assigned the same values is to issue them from the server when the row is inserted. It is the entire premise behind the server issuing AI values for PKs.

BUT since your thing is a multi-segment, "numeric string" that presents a problem. Rather than tearing the string apart to find the Max()+1 for one segment with a WHERE clause on parts of the string. Consider something like this:

Start with a table used to increment and issue the values:

 {DocId Int, SegmentB int, SegmentC Int}

This will simply track the values to use in the other table. Then a stored procedure to create/increment a new code (MySQL - this is a conceptual answer):

CREATE DEFINER=`root`@`localhost` PROCEDURE `GetNextProductCode`(in docId int,
        in Minr int, 
        in Rev int 
        )
BEGIN
    SET @maxR = 0;
    SET @retCode ='';

    if Minr =-1 then
        Start transaction;
        SET @maxR = (SELECT Max(SegmentB) FROM articlecode WHERE MainId = docId) + 1;
        UPDATE articlecode SET SegmentB = @maxR WHERE MainId = docId;
        Commit;
        Select concat(Cast(docId As char) , '.', 
                      Cast(@maxR AS char) , '.',
                      Cast(Rev As char)
                      );
    end if;
END

This is a rough idea of the process. As such, it only works on the second segment (I dunno what happens when you create a NEW SegmentB - does SegmentC reset to 1???). The idea is:

• pass numbers so there is no need to tear up a string
• pass -1 for the segment you need the next value for
• the sp gets the Max()+1 and updates the counter table so the next user will get a new value
• If for some reason you end up not saving the row, there will be gaps
• the sp uses a transaction (probably only needs to protect the update) so that only 1 update can happen at a time
• returns the new code. it could just return 2 values, but your going to glue them together anyway

There is much To Do:

• It only does SegmentB
• For a NEW DocId (-1), insert a new row with 1000 and 1(?) defaults
• Same for a NEW segmentB (whatever it is): insert a new row for that DocId with default values

To get a new code before you insert a row:

cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.Add("docId", MySqlDbType.Int32).Value = 3
cmd.Parameters.Add("Minr", MySqlDbType.Int32).Value = -1
cmd.Parameters.Add("Rev", MySqlDbType.Int32).Value = 1

dbcon.Open()
Using rdr = cmd.ExecuteReader()
    rdr.Read()
    Console.WriteLine(rdr(0))
End Using

The obvious downside is that each insert requires you to hit the DB in order to...well save to the DB. If they were int values it could be a Trigger.

Answer 2

I have two suggestions:

First suggestion: move your code to a stored procedure this way all your users will execute the same transaction where you can set your isolation level the way you want. Read This.

Second suggestion: I would create a unique index on your field Nummer. This way when I try to insert a duplicate value it will raise an error that I can deal with it by telling the user that he need to retry the same operation or retry it automatically.

Trying to lock the record or the table for your operation is not advisable, however you can check this article on code project you might find what you are looking for. Make sure that you provide a mechanism of releasing all locks if your program stops at the middle of the transaction.

Answer 3

I'm a SQL developer and my VB skills are about fifteen years out of date, but instead of creating the incremented number yourself in VB just let SQL generate them with an IDENTITY field. SQL will never allow duplicates and then you just need to return the SCOPE_IDENTITY():

ALTER TABLE dbo.tbArtikel
ADD [ArtikelID] INT IDENTITY(1,1) PRIMARY KEY;