LLBLGEN 4.0 SQL Server 2008 LLBLGEN Run time framework Self servicing vb.net windows application

Hi team

I need advise on how to use begin transaction in the LLBLGEN. What I am trying to achive is both read and write by other transaction should be prevented when a transaction is in progress.

Idea is to prevent duplicate insert of same data in multi user environment. Example: if user A inserts text "ABC" from terminal A and user B inserts same text from terminal B at same time with 0 time difference, it should not insert. PS: I dont want to use unique constraints

Please check the docs here

Walaa wrote:

Please check the docs here

well, this does not execute SELECT from other transactions after the line "newOrderRow.Save();" What I am looking for to prevent SELECT from other transactions once it reaches the first line after initiating isolation level in first line "OrderEntity newOrder = new OrderEntity();"

My problem is this. See the below code. I have at least 100 users operating the software. If 100 people execute this code at same time and insert same dataq, it creates duplicate in DB. I have another function which actually checks if a given data exists at the time of insertion. This function does not give true picture as the select statement is not locked after entering try catch block. But select statement from other trans get locked only at Dim result = title.Save()

Public Shared Function Save(description As String) As Boolean
        Dim transactionManager As New Transaction(IsolationLevel.ReadCommitted, "TitleSave")
        Try
            Dim title = New TitleEntity
            title.Description = ChangeCaseClass.Titlecase(ConvertNullDbNullClass.IsNull(description))
            title.Createddate = DateTimeClass.Currentdatetime()
            title.Createduserid = ConstantClass.Userid
            title.Modifieddate = DateTimeClass.Currentdatetime()
            title.Flag = ConstantClass.Validflag
            transactionManager.Add(title)
            Dim result = title.Save()
            transactionManager.Commit()
            Return result
        Catch ex As Exception
            transactionManager.Rollback()
            ErrorHandlerClass.LogMessage(ex.Message + ex.StackTrace)
            Throw
        Finally
            transactionManager.Dispose()
        End Try
    End Function

The only way to accomplish that is by a table lock which you shouldn't use, as it kills performance.

So if you need just 1 instance of a given piece of data, make sure just one can insert, either by using a unique constraint (so others will fail, you don't want to use this, but it is a way to prevent duplicates), or by using a process which does the inserts, so you post the data to insert to the process and it inserts the data for you, eliminating duplicates there.

Otis wrote:

The only way to accomplish that is by a table lock which you shouldn't use, as it kills performance.

So if you need just 1 instance of a given piece of data, make sure just one can insert, either by using a unique constraint (so others will fail, you don't want to use this, but it is a way to prevent duplicates), or by using a process which does the inserts, so you post the data to insert to the process and it inserts the data for you, eliminating duplicates there.

Didn't understand this line

or by using a process which does the inserts, so you post the data to insert to the process and it inserts the data for you, eliminating duplicates there.

Option 2: Also how to use table locks in llblgen? Since it is only for master creation tables, it wont affect my software performance

Instead of allowing multiple instances of your app insert the data, you can create a service, to which you send the data to insert. This service then checks whether it has inserted the data already and if not, it inserts it, otherwise it fails.

Though it's easier to simply use a unique constraint and fail if it's violated: the problem is: the user who wants to insert the duplicate data has to alter the data anyway: he can never proceed with his insert action as the data is always duplicate. So the user has to be offered a way to alter the data so it can be re-inserted.

Otis wrote:

Instead of allowing multiple instances of your app insert the data, you can create a service, to which you send the data to insert. This service then checks whether it has inserted the data already and if not, it inserts it, otherwise it fails.

Though it's easier to simply use a unique constraint and fail if it's violated: the problem is: the user who wants to insert the duplicate data has to alter the data anyway: he can never proceed with his insert action as the data is always duplicate. So the user has to be offered a way to alter the data so it can be re-inserted.

Option 1: Also how to use table locks in llblgen? Since it is only for master creation tables, it wont affect my software performance.

Option 2 Also I do not know how to create and use service. For sure i cant expect llblgen to guide me on this

You can't lock a table directly, as we don't offer a way to lock a table. What is the exact use case here, for which you want to prevent duplicates but don't want to use the construct which is created for that: unique constraints?

Besides, table locks won't help unless you do follow this procedure: - lock table - fetch the row with the data you want to insert - if there's a row, fail - if not, insert - release lock

WHich is exactly the same as when you use a unique constraint: - insert row - if there's already a row present the unique constraint fails, so the query fails.

In both situations the query fails and you have to report this to the user so he can alter the data to insert, as that's always needed.

Otis wrote:

You can't lock a table directly, as we don't offer a way to lock a table. What is the exact use case here, for which you want to prevent duplicates but don't want to use the construct which is created for that: unique constraints?

Besides, table locks won't help unless you do follow this procedure: - lock table - fetch the row with the data you want to insert - if there's a row, fail - if not, insert - release lock

WHich is exactly the same as when you use a unique constraint: - insert row - if there's already a row present the unique constraint fails, so the query fails.

In both situations the query fails and you have to report this to the user so he can alter the data to insert, as that's always needed.

Ok will go with unique constraints