Solved

BEGIN TRAN and testing

Posted on 2010-08-14
21
404 Views
Last Modified: 2012-05-10
when i had a statement in a trasaction, and wanted to test it. so i went to the web app, but it would not respond because of the lock. if i did rollback, the webpages respond fine right away. Unless the underlying query is made to run with NOLOCK, there is no other way to test the effects of the statements in the transaction, before committing, is there?

thanks
0
Comment
Question by:anushahanna
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
  • 4
  • +2
21 Comments
 
LVL 21

Assisted Solution

by:Dale Burrell
Dale Burrell earned 64 total points
ID: 33436128
Can you give a bit more information about what you are trying to do maybe include the SQL. Normally you would test the SQL outside the transaction and then add the transaction (begin/rollback/commit) around the whole thing.
0
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 33436130
Also its probably much easier to test the transaction in query analyser rather than the webpage.
0
 
LVL 3

Assisted Solution

by:man2002ua
man2002ua earned 147 total points
ID: 33436144
You can create table variable to do some logging into, after commit or rollback this  table  variable still have all records inserted, as MSSQL does not log it in the transcation.
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 70

Assisted Solution

by:Qlemo
Qlemo earned 216 total points
ID: 33436208
The concept of transactions is that you get a consistent data set. While you have a open transaction, no other session can read the changed data (with exception of NOLOCK or isolation level read uncommitted).
A test needs either to use test tables (or global temporary tables), or the test data needs to be "rolled back" manually, which is by performing reverting SQLs.
0
 
LVL 42

Assisted Solution

by:dqmq
dqmq earned 73 total points
ID: 33437218
>Unless the underlying query is made to run with NOLOCK, there is no other way to test the effects of the statements in the transaction, before committing, is there?

Two things are interfering with your test.  LOCKS and TRANSACTION ISOLATION LEVEL.  Even if you get around the locks, the default isolation level is READ COMMITTED, which means that changes are invisible to other sessions until they are committed.  

Manipulating either LOCKS or ISOLATION LEVEL for the sake of testing is not a very good idea because both fundamentally change the way the application behaves and interacts with other transactions.

0
 
LVL 3

Expert Comment

by:man2002ua
ID: 33437639
Do not "play" with isolation levels, it is not recommended at all. This is very bad practice.
If you need to see your data during/after execution, use table variable or global table.
0
 
LVL 6

Author Comment

by:anushahanna
ID: 33446636
thanks for the nice advice.

>>table variable to do some logging into
>>test tables (or global temporary tables)

Do you mean to hold the data, so if need be, can revert back?

0
 
LVL 70

Expert Comment

by:Qlemo
ID: 33446941
No. Holding the data could be achieved by doing a backup, and restore after test. Keeping old values somewhere is only appropriate if you have a static DB for test, that is noone else is fiddling around with it.

The more sophisticated way is to use some other user, owning it's own copy of the tables to change, and only change those. Since a schema owner's tables have precedence over the general available (assigned to dbo), that works great - if a table in the own schema exists, it will overlay the dbo one, without the application knowing of it.
0
 
LVL 3

Expert Comment

by:man2002ua
ID: 33447022
You can store any data into the table variable (either just some log entries like "reaching point X at hh:mm:ss" or store data in the middle of the transaction). Again, table variables are not the part of the transaction scope, so use them as you want. And this data will not revert back after ROLLBACK. Tell me what exactly you are trying to achieve.
0
 
LVL 6

Author Comment

by:anushahanna
ID: 33448749
>>The more sophisticated way is to use some other user

So you mean if the app is using dbo.tblTracker, then I could create testSchema.tblTracker, and do make the app to point to the testSchema data, instead of the default dbo used for the app?
0
 
LVL 6

Author Comment

by:anushahanna
ID: 33448775
>>table variables are not the part of the transaction scope

so we lose the data at the end of the session; but no big deal if the table variables are lost. But I wanted to test the data change, and see if the app behaves correctly, and then commit it after the users take a look at it and are happy. this way, i do not have to make the database single user mode, and can still let other users in while i am making the change, and test and then commit...
0
 
LVL 3

Assisted Solution

by:man2002ua
man2002ua earned 147 total points
ID: 33448904
Nooo :) We do not lose the data in the table variable. I mean:
begin tran
  insert into maintable select ....
  insert into @tablevariable select * from maintable
rollback tran
 -- after rollback maintable is empty, BUT @tablevariable still have data (inserted from maintable)
And here you can analyze your query behave correctly (but checking contents of table variable).

Do you need other users to check uncommited data or it is only for you?
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 33450048
The "different schema" thingy only works smooth if you do *not* explicitely refer the owner of the objects. Else it does not make much sense, as you need to adapt the SQLs each time.
0
 
LVL 6

Author Comment

by:anushahanna
ID: 33458168
man2002ua, only i will be interested in the data consistency.
0
 
LVL 6

Author Comment

by:anushahanna
ID: 33458182
Qlemo, how will your idea work in sql 2000 vs sql 2005? (with schema's meaning being different)
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 33458725
The concept of "schema" is not applying to MSSQL 2000. But the owner of tables still exists. Schemas are "only" an additional layer you can put between the db and the objects. So instead of server.db.schema.object you can still use server.db.owner.object. As long as schema and owner are named the same, it's not different (in respect to what I suggested).
0
 
LVL 6

Author Comment

by:anushahanna
ID: 33464615
Qlemo, to make sure i get your point, consider this:

create schema schematest
create table schematest.TestTable (TestCol_schema int)
create table TestTable (TestCol_dbo int)
select * from TestTable
--select * from schematest.TestTable
--drop table TestTable
--drop table schematest.TestTable
--drop schema schematest

In the above scenario, did you anticipate my main 'live' data to be in dbo.TestTable, and that my test data should be in schematest.TestTable?
0
 
LVL 70

Accepted Solution

by:
Qlemo earned 216 total points
ID: 33466291
First you need to determine if you have to consider 2000 or not.
create schema does only work on 2005 and later. Just use another user instead to be compatible. If you name the user "schematest":

select * into schematest.TestTable from TestTable;
begin trans
update schematest.TestTable /* .... */
/* .... */
commit;



0
 
LVL 6

Author Comment

by:anushahanna
ID: 33478314
OK. So the above code in 33466291 will work both in 2000 and 2005, but in diff context, right? but both will be OK?
(in sql 2000, i have to create user instead of schema)

then check the result in schematest.TestTable, and then if i am happy with it, then do the regular stuff in dbo tables.?
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 33478418
Absolutely correct!
0
 
LVL 6

Author Comment

by:anushahanna
ID: 33478496
great help! thanks expert!
0

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question