Solved

BEGIN TRAN and testing

Posted on 2010-08-14
21
389 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 69

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 69

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 69

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 69

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 69

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 69

Expert Comment

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

Author Comment

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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Server 2012 VS Server 2016 for SQL Cluster 4 40
MS SQL Merging data from table into another table 1 32
2016 SQL Licensing 7 40
SQL View nearest date 5 35
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…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

813 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

18 Experts available now in Live!

Get 1:1 Help Now