Specifying 'QueryTimeout' issues a LOCK_TIMEOUT on the server

[gjdanis]

In the pdo_sqlsrv and sqlsrv drivers it looks like setting 'QueryTimeout' actually issues a LOCK_TIMEOUT command on the server. Here's an example with PDO:

$pdo = new PDO(...);
$pdo->setAttribute(PDO::SQLSRV_ATTR_QUERY_TIMEOUT, 30);
$pdo->exec(
  '
    WAITFOR DELAY \'00:00:40\'; SELECT 1
  '
);

This likely isn't what we want as read queries that don't take any locks and exceed this timeout won't be killed.

Is there a recommended approach to ensure that read queries don't exceed a user specified query timeout?

[gjdanis]

It also looks like every statement will produce a command to SET LOCK_TIMEOUT resulting in an extra roundtrip to the server. Is it possible to make this part of the DSN so that the command need only be issued once but can be overridden by an individual statement?

[yitam]

Hi @gjdanis, where did you see that LOCK_TIMEOUT was used?

[gjdanis]

Hey @yitam I opened up SQL Server Profiler - you should see it executed before each query.

[yitam]

hi @gjdanis

Using your repro script, I could see that the query took roughly 40s to return. It seems that query timeout has no effect on PDO::exec().

That being said, if using a prepared statement, then query timeout works. For example,

$t0 = microtime(true);

$options = array(PDO::SQLSRV_ATTR_QUERY_TIMEOUT => 2);
$query = 'WAITFOR DELAY \'00:00:40\'; SELECT 1';

$stmt = $conn->prepare($query, $options);
echo $stmt->getAttribute(PDO::SQLSRV_ATTR_QUERY_TIMEOUT) . PHP_EOL;  
$result = $stmt->execute();

$t1 = microtime(true);

$elapsed = $t1 - $t0;

echo "Time elapsed: $elapsed secs with result:\n";
var_dump($result);

print "Done\n";

The output I got was

2
Time elapsed: 2.0018880367279 secs with result:
bool(false)
Done

Will investigate some more and get back to you on this.

[gjdanis]

Hey @yitam any update on this?

I can confirm that statement options seem to work. Here's the trace I ran and it looks like we issue this setting prior to running the query.

[yitam]

Thanks @gjdanis for your patience. We have confirmed it's a bug in our implementation for PDO::exec().

[yitam]

hi @gjdanis if you have time please test again with our new release 5.7.1-preview.

[yitam]

Closing this issue as this should have been fixed in the latest release 5.8.0. Please feel free to reopen if you have any questions concerning this.

[gjdanis]

@yitam thank you for your work on this - I can confirm the issue is fixed.

[gjdanis]

@yitam just FYI - opened up a similar issue here: #1100