January 21, 2016 at 6:18 am
Good morning and good luck to all,
SQL 2014:
I have this problem:
from my procedure in TSQL update the table TEST and where there is the PK of the table and so everything is right;
If I try, from another session, to update the same table TEST, with a PK different course, it should not be left waiting for the procedure in terms TSQL;
What's even stranger is that only with the Table TEST makes me, with other tables works great.
Look kindly vs.
January 21, 2016 at 6:31 am
Can you rephrase your post please? It looks like some info did not make it into the post and the wording is not clear as to what you're experiencing or asking.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 21, 2016 at 6:37 am
You're going to have to explain the problem in a lot more detail please, I can't understand. Code may very well help.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 21, 2016 at 6:54 am
This is the cose:
from session number 1:
BEGIN TRY
BEGIN TRANSACTION
update TEST set A=1 where A=2;
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF (XACT_STATE()) = -1
BEGIN
ROLLBACK TRANSACTION
END
IF (XACT_STATE()) = 1
BEGIN
COMMIT TRANSACTION
END
END CATCH
from another session number 2:
update TEST set B=1 where B=2;
this remains here waiting for the commit of the session 1
why??
with other table this does not happen
January 21, 2016 at 6:59 am
Is session 2 still waiting after session 1 is complete? As an aside, in session 1 you do not need an explicit transaction. Statements in SQL Server are atomic meaning the entire update will succeed or fail, ie no such thing as a partial update to worry about from a single update statement.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 21, 2016 at 7:00 am
emanuele 8962 (1/21/2016)
This is the cose:from session number 1:
BEGIN TRY
BEGIN TRANSACTION
update TEST set A=1 where A=2;
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF (XACT_STATE()) = -1
BEGIN
ROLLBACK TRANSACTION
END
IF (XACT_STATE()) = 1
BEGIN
COMMIT TRANSACTION
END
END CATCH
from another session number 2:
update TEST set B=1 where B=2;
this remains here waiting for the commit of the session 1
why??
with other table this does not happen
Most likely cause for NOT blocking on single (or few) row updates on different spids is that you are getting an INDEX SEEK plan on each and ROW LOCKS by the engine. This is a great thing from a concurrency perspective, right?? You can see this by showing the ACTUAL EXCUTION PLAN for each query.
So you in your example that IS blocking, you are either:
1) scanning data instead of seeking on either or both queries
2) getting a type of lock that is causing blocking (page or table)
3) updating the same row in both updates (writers always block writers in default isolation level on SQL Server)
BTW, you can see the details of the locks and blocking by using the AWESOME, FREE sp_whoisactive sproc by Adam Machanic (over on sqlblog.com).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 21, 2016 at 11:59 am
I'm sorry but I did not understand, I installed whoisactive, but I do not see information about. Sorry for the language and the inexperience
The table haven't index, PK, trigger, FK etc.....
What can be or how to make alternative
January 21, 2016 at 12:20 pm
You need to run sp_whoisactive on it's own connection while both of the other queries are running (and blocking each other). It will show you details. Adam has an awesome 30-day blog post series on all the options for that sproc.
If you have NO indexes on the table that is going to mean a table scan for every update, and that is going to be bad for performance AND concurrency!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 21, 2016 at 12:49 pm
Thanks very much Kevin, Tomorrow i do and i update you. Good night
ES
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply