JimmyJimm JimmyJimm - 1 month ago 6x
Vb.net Question

Same data is inserted during insert

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

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)
Using transaction = connection.BeginTransaction()

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 ...

Catch ex As Exception
Throw 'Rethrow exception.'
End Try
End Using
End Using
End Sub


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 
    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;
        Select concat(Cast(docId As char) , '.', 
                      Cast(@maxR AS char) , '.',
                      Cast(Rev As char)
    end if;

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

Using rdr = cmd.ExecuteReader()
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.