0

We are a small firm, with about 10 people using a database server.

I have this application that connects to a database and fills about 40 rows of data in an table. Then calls a stored procedure to process the data before inserting the data back into another table and then truncates the table.

Is it possible if two people run the application at the same time, the data can be nonsensical. If so, how can I prevent this?

  • Lock the table (for reading). Second session will not access locked data. – Akina Jun 28 '18 at 18:33
  • what engine are you using? Oracle / MySQL / MSSQL / PostgreSQL? – AMG Jun 28 '18 at 19:58
  • @AMG mysql innodb – Anirban Jun 28 '18 at 20:32
  • @Akina will the second session continue once the lock is lifted or not continue at all. The entire process finishes fairly quickly, takes 0.2 seconds, and I added additional logic to the code to check if the table is truncated before executing the query – Anirban Jun 28 '18 at 20:33
  • So... please confirm if this is what your program automatically executes: creates session -> begins transaction -> inserts 40 records in table A -> processes the data -> saves the data into table B -> truncates the table A -> Commits transaction-> Closes connection. Is this correct? – AMG Jun 28 '18 at 21:21
0

I would recommend to use temporary tables instead of insert and truncate. They are invisible to other sessions, so you can have many sessions creating the same "TEMPTABLEA", inserting records, deleting records, and even destroying the TEMP TABLE without interfering other users work. When you kill the session, the Temp Table automatically is destroyed.

I'll put a fragment of VBA code that you must addapt to yours. (make sure you have these references Microsoft ActiveX Data Objects 6.1 Library and Microsoft ActiveX Data Objects Recordset 6.1 Library )

This is the code:

Public Sub Routine()
    On Error GoTo ErrorHandler
    Dim Cnxn As ADODB.Connection
    Dim strCnxn As String

    Dim sqlStr0 As String
    'Dim ...all SQL strings you need
    'Dim ...all recordsets you need


    'Open Connection and begin transaction
    strCnxn = "Provider='sqloledb';Data Source='MySqlServer';" & _
                "Initial Catalog='[Your Data base here]';Integrated Security='SSPI';"
    Set Cnxn = New ADODB.Connection
    Cnxn.Open strCnxn
    Cnxn.BeginTrans

    'Sql String to create a TEMPORARY TABLE using YOUR ORIGIN TABLE as a TEMPLATE
    sqlStr0 = "create temporary table TempTableA as select * from tableA where 1 = 2;"
    Cnxn.Execute sqlStr0

    'Put your code here to insert the 40 records into TempTableA
    'Put your code here to move records from TempTableA to definitive TableB
    'Do not worry about TempTableA anymore. It will be destroyed.

    Cnxn.CommitTrans
    Cnxn.Close
    Set Cnxn = Nothing

    Exit Sub

ErrorHandler:
    ' clean up
    If Not Cnxn Is Nothing Then
        If Cnxn.State = adStateOpen Then
            Cnxn.RollbackTrans
            Cnxn.Close
        End If
        Set Cnxn = Nothing
    End If
    If Err <> 0 Then
        MsgBox Err.Source & "-->" & Err.Description, , "Error"
    End If
End Sub

Good luck!!

| improve this answer | |
0

One approach:

  • At the beginning of the operations use SELECT GET_LOCK('no-nonsense');
  • At the end, do SELECT RELEASE_LOCK('no-nonsense');
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.