Solved

BEGIN TRAN and testing

Posted on 2010-08-14
21
366 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
  • 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
 
LVL 68

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 68

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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 68

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 68

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 68

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 68

Expert Comment

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

Author Comment

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

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

746 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now