|
I'm having a locking issue I think.
Rather than post a whole heap of code, here is basically what I'm trying:
Begin trans
Insert <stuff> into table1
Insert <stuff> into table2
Insert into table3 select <stuff> from table1, table2, table3 plus a few other unmodified tables.
Commit trans
Basically what seems to be happening is that the select on 3rd statement returns no records (because they are locked). After committing, same select returns exactly what it should.
I could probably do this with several non-nested transactions, but that kind of defeats the purpose of the transaction.
I have tried SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED before and during the main trans. Also tried wrapping the first two inserts in begin / commit and SELECT FROM WITH(NOLOCK).
Have googled nolock and also TRANSACTION ISOLATION LEVEL but not really come up with anything useful.
Does anyone have any ideas or can point me in the direction of a URL with some information please?
modified on Monday, June 16, 2008 4:33 AM
|
|
|
|
|
Strange - I just tried to replicate said behaviour and I had no issues with inserting in a table, selecting from said table and inserting into another table while within the same transaction.
Further more the things you state you have tried should be - AFAIK - enough/the correct method to solve issues similar to this.
Are you sure - 100% - that your "insert into table 3" query runs as it should? And that there aren't another problem then with the isolation level?
How do you call the transaction - via a code layer? Or directly as a query? - If the first, then try to run a simple example directly as a query and see if that gives you problems.
Could be the problem is completely elsewhere?
|
|
|
|
|
I did exactly the same thing, it all worked as expected - and I agree, AFAK it is the correct way to do it. I think you are correct, the problem is elsewhere. I do know in the past I have employed this technique many times without problems.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Thanks for the replies.
Took the whole thing apart and ran each query separately, works as expected.
Put back together, commented out the 3rd INSERT to just leave select. Pulls no records as expected.
Commented out select and hardcoded insert with some data that I knew insert1 & 2 would create and works fine.
Headbutted wall for a while, cracked open a bottle of wine, rebooted server and whadaya know...Works perfectly. Removed WITH(NOLOCK)s and... works perfectly. Removed READ UNCOMMITTED, works perfectly.
No idea what caused it. Could have been some other lock, although nothing else should have had a lock.
Its all marked for some serious testing and I'll see if its reproducable!
Anyway, thanks for the help. Hope I can reciprocate one day.
|
|
|
|
|
Hehe. Stuff which should work, but doesn't sporadically .... always good fun to debug 
Well, at least you got it running... more or less
|
|
|
|
|
I am getting tired of fighting with VC6.0 SQL syntax.
Why is this failing with - missing operator - error?
What operator?
I just want records which match multiple conditions.
It did not like comma separated WHERE also.
Could somebody please help me.
CString strSQL = "SELECT * FROM [Log] WHERE [Callsign] = Callsign and [Band] = Band ORDER BY [ContactID] DESC ; ";
Thanks for reading
Vaclav
<div class="ForumMod">modified on Sunday, June 15, 2008 12:58 AM</div>
|
|
|
|
|
try
CString strSQL = "SELECT * FROM [Log] WHERE [Callsign] = 'Callsign' and [Band] = 'Band' ORDER BY [ContactID] DESC ; ";
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
Thanks - it works.
So why the stupid error code could not tell me that?
Especially when the VC replaces the brackets with single quotation marks.
I am not sure quotation marks should be called "operators".
Thanks again.
|
|
|
|
|
No problem.
When in condition you have value string then it must placed inside single quotes.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
So the SQL ends up looking like this 'Table' = 'Condition string'.
I'll try [Table] = [Condition string] to see hat VC will do with that.
|
|
|
|
|
No
Vaclav_Sal wrote: 'Table' = 'Condition string'.
but
select * from TableName where ColumnName='ConditionString'
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
hi friends,
I have a problem with web user control with firing events using AjaxPopupcontrol.
my need is, when I mouse hover the text - some other text will display as a
popuptext from database. this is working well in normal aspx pages.
but when I place the popupextenwer and other controls to the Web User Control and
place the web user control to one aspx page. it doesn't work. mouse hover worked but not getting values from database. it returns the following error message!!!
Web Service Call Failed : 500
can any one explain how to do this. giv me a code for this.
Thanks in Advance!!!
Sabarees
|
|
|
|
|
Wow - SQL questions in the ASP.NET forum, and now ASP.NET questions in the SQL forum ?
Christian Graus
Please read this if you don't understand the answer I've given you
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
I guess one day he/she will get idea on posting questions in right forum.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
Hello All
I have a server with MS SQL Server 2005 DataBase on it
and I set the connection string for the client to the
database on the server.
now my question is, does the processing (CPU activity) while making
a query (or a transaction) on the server or on the client?
and if it is on the client, how do I change it to the server?
Thanks
|
|
|
|
|
The parsing, compilation and execution of the query is performed on the server, not the client.
|
|
|
|
|
|
hi friend i want to rollback all the transaction of today when there is some Exception thrown while executing today Query how i can do this
in C#.
wasim khan
|
|
|
|
|
I'm not quite sure what yuo are trying to achieve, but you enclose your sql in a try catch with a transaction:
<pre>try
{
SQLConnection conn = new SQLConnection(connectionString);
conn.begintran():
...do your sql
conn.committran();
}
catch (Exception)
{
conn.rollbacktran();
}
finally
{
conn.close();
conn.dispose();
}</pre>
I may have the syntax slightly wrong, I don't have c# on this laptop to check, but yuo should get the idea.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Do you mean you want to roll back an earlier transaction if a later transaction fails?
|
|
|
|
|
|
Hi,
I have a stored procedure that I can not get to work. I am passing a value for the sort order in the GROUP BY clause, but code does not want to create the stored procedure. What am I doing wrong:
create PROCEDURE [dbo].[nsp_SearchManager_716]
(
@RequestedNB VARCHAR(50),
@Sort VARCHAR(50),
@SortOrder VARCHAR(50)
)
AS
SELECT
DISTINCT *
FROM
tblResult WITH (NOLOCK)
WHERE
sRequestedNB = @RequestedNB
ORDER BY
sSalary @SortOrder;
I would appreciate any sort of help.
Thanks
Brendan
|
|
|
|
|
I'm fairly certain that it wont evaulate the variable @SortOrder to get the direction. however you can do
EXEC ('SELECT DISTINCT * '+
'FROM tblResult WITH (NOLOCK) '+
'WHERE sRequestedNB = ' + @RequestedNB +
'ORDER BY sSalary ' + @SortOrder)
which will create then execute the string including evaluating the varibles. you could also put the sring into a varchar varible so that you can do SELECT or EXEC on it for testing purposes. limiting the size of your sort order varible to 3 (ASC, DSC) would help prevent someproblems from wherever this gets called. Hope this works for you.
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.
|
|
|
|
|
The syntax won't work unfortunately - I wish it would. there are two real options, create some dynamic sql or
if @SortOrder = 'ASC'
begin
SELECT
DISTINCT *
FROM
tblResult WITH (NOLOCK)
WHERE
sRequestedNB = @RequestedNB
ORDER BY
sSalary ASC
end
else
begin
SELECT
DISTINCT *
FROM
tblResult WITH (NOLOCK)
WHERE
sRequestedNB = @RequestedNB
ORDER BY
sSalary DESC
end
Alternatively, would it be possible to do the order by back in your code?
Bob
Ashfield Consultants Ltd
|
|
|
|
|
If salary is a number simply multiply -1 or 1 on it for desc and asc sorting.
It is an easy way to do dynamic sorting of number fields (and dates).
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
