Friday, 15 July 2011

php - Table Locking with Transactions, Deleting then Inserting in same Transaction -


updated question

So I have escalated this issue and now my code looks like this :

  $ mssql-> Starting restriction (); $ Mssql- & gt; SQL ("Remove from [table] where [field] = 'test'); // Type the result from the above query, // it will confirm that the line was deleted print_r ($ mssql-> results); $ Mssql-> Sql ("Select [from the table] where [field] = 'test' ''); // Write the result from the above query, // This row must be blank because the row was just removed print_r ($ mssql-> gt; result); $ Mssql- & gt; EndTransaction ();   

The above script works perfectly on a SQL Server database but on another (duplicate database on a different server), it does not work.

Manages to retrieve a line from another database table, even if that line has been deleted now ...

Additional Information

  1. From what I can see, the only difference in the database option is the broker enabled option which I believe has nothing to do with it.
  2. I do not know how accessibility or viewing options can be relevant to the transaction?
  3. Is this table with locking?

    SQL trace
      declared @ p1 int set @ p1 = 1 command sp_prepexec @ p1 output, n 'p1 navigator (6)', The table from N'DETE where [FIELD] = @ P1 ', select' N'm 8799 'Go @ P1, declared @ P1 inset set @ P1 = 2 exec sp_prepexec @ p1 Output, NULL, N' SELECT db_name () Select 'go @ p1 go exec sp_unprepare 2 go exec sp_unprepare go go @ p1 int set @ p1 = 1 exec sp_prepexe c @ p1 output, n' p1 environments (6) ', n 'LEX * FOR [TABLE] WHERE [FIELD] = @ P1', Nm 87996 'Select @ P1 to go to APAPAP This is the first time I have worked with SQL transactions, so I apologize if I am naive.  

    I have a transaction that removes one item from the database and then inserts the updated item but under the same primary key.

    Consider the following (Ignore my wrapper functions):

      $ mssql-> BeginTransaction (); $ Mssql- & gt; SQL ("Remove [table] where [field] = 'test'); $ Mssql- & gt; SQL (" INSERT [[TABLE] ([FIELD]) values ​​('test'); $ Mssql- & gt; And transaction ();   

    However, above, I'm getting a duplicate primary key error. Is that because he did not make the first query?

    Is it not possible for the above two questions to be the same transaction?

    I can not execute the above with a simple update command because I have to delete some lines and there is no way to know which rows to delete ...

    No, it should be something else in the game. You do not have to commit, within the same transaction you will always see your own changes, so DELETE has already started when you start INSERT.

    You must first validate that DELETE actually deletes the line. Silently where the volume does not match (i.e. 0 rows have been destroyed, check back). This can be understood from some collation issues with VARCHAR vs. NVHAR (ASCI versus Unicode) ed. It seems difficult.

No comments:

Post a Comment