Lock tables with transactions, delete then insert into the same transaction

Question

Updated Question

So, I debugged this problem further, and my code now looks like this:

$mssql->beginTransaction(); $mssql->sql("DELETE FROM [TABLE] WHERE [FIELD] = 'Test'"); // Write the result from the above query, // this will confirm the row was deleted print_r($mssql->result); $mssql->sql("SELECT FROM [TABLE] WHERE [FIELD] = 'Test'"); // Write the result from the above query, // this SHOULD be empty as the row was just deleted print_r($mssql->result); $mssql->endTransaction(); 

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

The second database allows you to retrieve a row from a table, although this row should just be deleted ...

Additional Information

• From what I see, the only difference in the database options is the Broker Enabled parameter, which I believe has nothing to do with this.
• I don’t know how to access or view transaction parameters, can they be relevant?
• Could this be due to table locking?

SQL Trace

 declare @p1 int set @p1=1 exec sp_prepexec @p1 output,N'@P1 nvarchar(6)',N'DELETE FROM TABLE WHERE [FIELD] =@P1 ',N'M87996' select @p1 go declare @p1 int set @p1=2 exec sp_prepexec @p1 output,NULL,N'SELECT db_name()' select @p1 go exec sp_unprepare 2 go exec sp_unprepare 1 go declare @p1 int set @p1=1 exec sp_prepexec @p1 output,N'@P1 nvarchar(6)',N'SELECT * FROM [TABLE] WHERE [FIELD] =@P1 ',N'M87996' select @p1 go exec sp_unprepare 1 go 

---

Original question

This is the first time I've worked with SQL transactions, so I'm sorry if I'm naive.

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

Consider the following (ignore my shell class functions):

 $mssql->beginTransaction(); $mssql->sql("DELETE FROM [TABLE] WHERE [FIELD] = 'Test'"); $mssql->sql("INSERT INTO [TABLE] ([FIELD]) VALUES ('Test')); $mssql->endTransaction(); 

However, with the above, I get a Duplicate Primary Key error. Is it because he did not fulfill the first request?

Is it impossible to have both of the above queries in the same transaction?

I cannot do the above with a simple UPDATE command, since I have to DELETE a few lines, and there is no way to know which lines to delete ...

Answer 1

No, it should be something else in the game. You do not need to make transactions, in the same transaction you will always see your own changes, so DELETE is already completed by the time INSERT starts.

First, think that you should verify that DELETE does delete the row. It could silently execute the WHERE clause (i.e. Delete 0 lines, check for PDO::exec return). This can be explained by some comparison problems in combination with VARCHAR vs. NVARCHAR (Ascii vs. Unicode). It’s hard to guess.