Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Xarrion
Starting Member
2 Posts |
 Posted - 2011-08-03 : 12:01:57
|
After running Delete against a table, select queries made against the same table run indefinitely. For example, if i do the following:BEGIN TRANSACTIONDELETE [EMR].[dbo].[medical_immunizations] WHERE patient_id=8675309GO and then immediately after I run SELECT * FROM [EMR].[dbo].[medical_immunizations] WHERE patient_id=8675309 It simply hangs at "Executing Query," never seems to conclude with 'No results found' If I query without the WHERE clause, it will give me immediate results, but will continue to show as 'Executing Query...' If I rollback the transaction, these queries will complete immediately. The only work around I've found to making these changes is to use the 'Edit Top 200' features in SSMS and delete them there. I am especially wary about this as I don't have the ability to rollback the transaction if i make a mistake.Is there an additional step I should be taking to perform the desired action? |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-08-03 : 12:23:37
|
| You need a COMMIT TRANSACTION after the delete statement. Even better, remove the BEGIN TRANSACTION statement. Each statement in SQL Server runs in an implicit TRANSACTION. So in your case, either the delete will succeed entirely, or nothing will be deleted, even without the BEGIN TRANSACTION statement. So you don't need it. |
 |
|
|
Xarrion
Starting Member
2 Posts |
Posted - 2011-08-03 : 18:18:33
|
quote:
Originally posted by sunitabeck
You need a COMMIT TRANSACTION after the delete statement. Even better, remove the BEGIN TRANSACTION statement. Each statement in SQL Server runs in an implicit TRANSACTION. So in your case, either the delete will succeed entirely, or nothing will be deleted, even without the BEGIN TRANSACTION statement. So you don't need it.
Connected to my test server, once I did "COMMIT TRANSACTION" it worked fine. However, the reason I was doing this as a transaction was for the rollback functionality, which I now see is not the intended purpose of transaction. What I ended up doing to create mini backups, was installing SSMS tools which lets me right click and generate insert statements. I inject "WHERE patient_id=8675309" to the wizard, and it generates a script to replace the data. After making these backups, I delete the data.Thank you very much for pointing me in the right direction sunitabeck, and to anyone reading, I highly recommend SSMS tools. |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-08-03 : 19:40:10
|
| I disagree with sunitabeck - keep the BEGIN TRANSACTION in there. In fact, follow the delete immediately with a ROLLBACK TRANSACTION and after that have a commented line with COMMIT TRANSACTION.This way, when you test it - it will automatically rollback the transaction and you can verify the right number of rows were deleted. This will also prevent the oops moment where you thought your where clause was doing what you expected and instead of the 17 records to be deleted the system deleted 471,233 rows.When I do these kinds of things - I always wrap it up in an explicit transaction. This gives me the opportunity to test and validate the code before I really get into trouble.I also will put a query before the delete to show the rows I expect to be deleted, and follow it with another query to validate the rows I expected to be deleted were deleted. The same goes for updates and inserts.The only problem you were having is that you left the transaction open - which causes the system to hold a lock on that row. If you immediately issue a rollback or commit that wouldn't happen. By having the rollback un-commented and the commit commented out, the default is to rollback the changes immediately. Once you are satisfied with the results - comment out the rollback, un-comment the commit and execute for real.Jeff |
|
|
|
|
|
|
|
|
Can't select after delete