SQL server transactions in PHP

Question

I'm trying to grasp the idea of transactions fully. Therefore the following question... (ofcourse newbie, so don't laugh :D )

I have set up a (simplified) transaction in PHP (using the PHP SQL driver from microsoft). I want to get the rows I'm going to delete for some extra processing later:

sqlsrv_begin_transaction($conn);
$sql = "SELECT * FROM test WITH (XLOCK) WHERE a<10";
$statement = sqlsrv_query($conn,$sql);
$sql = "DELETE FROM test WHERE a<10";
sqlsrv_query($conn,$sql);

$result = get_result_array($statement);
sqlsrv_commit($conn);
$result2 = get_result_array($statement);

1) I do get the expected result in $result but an empty array in $result2. Why?

I would expect only a result in $result2 because then the transaction has actually been executed. I guess the result in $result is a sort of 'temporary' result in memory and not actually a result from the actual database.

2) It could be that between the moment the transaction was started and the actual commit, an other query from another connection has changed the rows which match (a<10)? That means that the results I'm expecting according to $result will be different from the actual changes in the database.

Or is it that (a) the transaction occurres with an in-memory copy of the database (not affected by in-between queries from other connections), or (b) the locks obtained since the beginning of the transaction are already in action for other queries from other connections?

After typing this I'm expecting answer b....?

Answer 1

I'm not familiar with the sqlsrv driver, but if it works anything like most other PHP DB drivers, the result of the sqlsrv_query call is not a result set in some form of array, but a PHP resource (see http://www.php.net/manual/en/language.types.resource.php). Calling get_result_array still retrieves data from that resource, in this case the database, and it does so immediately. The COMMIT only affects writes to the database, not reads, so you see your result immediately in result1. After you commit your transaction (i.e, the DELETE), the next call correctly returns an empty result set.

Answer 2

I tested it out with some mysql tools (which i'm more familiar with): 1. When I start a transaction and do a 'select' of one particular record I directly get the result. Then from an other connection I delete the same record (with autocommit) it is gone for that connection but for the first connection the record is still there (I did the 'select' again without committing the transaction). Only after committing the transaction of the first connection and doing the 'select' again the record is gone. 2. When I do the same but acquire an exclusive lock for the first 'select' query then the delete query of the second connection waits until the transaction of the first connection has been committed.

Conclusion: In situation (1) for the second select query of the first connection, the database IS returning a result as it was at the moment of the start of the transaction... thus WITHOUT taking into account other (write) queries running AFTER the start of the transaction. Situation (2) is exactly the answer 2b from my original question. :)