Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

BEGIN TRAN and testing

Posted on 2010-08-14
21
Medium Priority
?
412 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 256 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 588 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 71

Assisted Solution

by:Qlemo
Qlemo earned 864 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 292 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 71

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 588 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 71

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 71

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 71

Accepted Solution

by:
Qlemo earned 864 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 71

Expert Comment

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

Author Comment

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

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

661 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