CODEWITHSUNDEEP
phpsqlsql-servertransactions
Ben Carey
Ben Carey

Reputation: 16948

Table Locking with Transactions, Deleting then Inserting in same Transaction

Updated Question

So I have debugged this issue 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 absolutely perfectly on one SQL Server Database, but on another one (a duplicate database on a different server), it does not work.

The second database manages to retrieve a row from the table, even though that row should have just been deleted...

Additional Information

  1. From what I can see, the only difference in database options is the Broker Enabled option which I believe has nothing to do with this.
  2. I do not know how to access or view Transaction options, could these be relevant?
  3. Could this be to do with 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 have worked with SQL Transactions, so I apologise if I am being naive.

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

Consider the following (ignore my wrapper 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 am getting a Duplicate Primary Key error. Is that because it has not committed the first query?

Is it therefore not possible to have both of the above queries in the same transaction?

I cannot execute the above with a simple UPDATE command sadly as I must DELETE some rows and there is no way of knowing which rows to delete...

Upvotes: 4

Views: 3070

Answers (1)

Remus Rusanu
Remus Rusanu

Reputation: 294177

No, it must be something else at play. You do not have to commit, within the same transaction you will always see your own changes, so the DELETE already complete by the time you're INSERT start.

First think you have to validate is that DELETE really deletes the row. It could silently fail to match the WHERE clause (ie. 0 rows deleted, check the return of PDO::exec). This could be explained by some collation issues combined with VARCHAR vs. NVARCHAR (Ascii vs. Unicode). Is difficult to guess.

Upvotes: 3

Related Questions