1

Technology

I am using the mssql extension to php (on linux) to send queries to SQL Server.

The Setup.

I have a table that builds up disposable data rather quickly. It took me longer than expected to build a cron to clean out this data every hour so it has been accumulating over months to be well over 1G of data not sure how many rows but probably over 1 million.

The Problem

So I ran a delete query for anything older than 1 day. 

DELETE FROM CacheForms WHERE TimeStamp < 1503454349

However after 60sec the query times out and the script fails. (update the same happens at 10min.)

The Question

When this delete query times out (a php concept) what happens to the database? Does the database restore the deleted data from transaction log? or is the data truly deleted? Or does the query keep running in sql server?

danielson317
  • 343
  • 2
  • 11

3 Answers3

6

When a timeout occurs, the client API sends an attention signal to SQL Server to cancel the executing query. Upon receipt, SQL Server cancels the query and rolls back the transaction. Data modification statements are run in autocommit mode by default so each statement is all-or-none.

Dan Guzman
  • 28,989
  • 2
  • 46
  • 71
1

When this delete query times out (a php concept) what happens to the database?

Dan Guzman's answer above is what is going on when the delete query times out.

Does the database restore the deleted data from transaction log? or is the data truly deleted? Or does the query keep running in sql server?

The data isn't deleted until a commit is implicitly issued. Because a timeout occurs before the commit is issued, an implicit rollback is issued instead and the result is no data changes are made. More information on how transactions work in SQL Server can be found here.

How do I purge the data?

I know you didn't ask this, but you are probably wondering it. The way to delete the data is to run the query directly against the database using a SQL client such as SQL Server Managment Studio, sqlcmd, or a variety of other IDEs. These don't generally specify a timeout setting when establishing a connection.

A word of warning though, if your purging a significant amount of data from the table, you'll likely lock that table until the operation completes. If this is a very large table or a very active database, you may want to look into some of the various ways to purge data from it. Here's a good article from Vic Newell that, while being old, still is quite relevant to your situation.

Finally, if you're running Enterprise Editition or any edition of SQL Server 2016 SP1 or later, you should look into Table Partitioning. This will allow you to quickly purge your old data using a sliding window technique without suffering from many of the performance issues you'll see with a table that's not partitioned.

John Eisbrener
  • 9,547
  • 6
  • 31
  • 65
-2

Assuming PHP aborts/closes the SQL connection, SQL should rollback any work it had done in the delete.

Greg
  • 3,292
  • 5
  • 33
  • 57