Will I be bloced?
-
Suppose I have a table called Account, Columns are:
AccountId, AccountBalance, UserId ........
And I have a batch application run at back-end to do:
list = get a user list;
foreach(user in list)
do
{
begin trans
1,get all the accounts for the user
2,update accountBalance according to some logic (coule be 1 or 2 seconds)
commit or rollback
}
(this batch app proberbly use jdbc with default lock policy: READ_COMMITTED)
At the same time, I have web UI which could let user create account in account table.(only INSERT)
my question is:
if I want to create another account in web UI, is there any chance I will be locked (blocked) when the back-end app is running?
I've heard that INSERT and SELECT will not be blocked for the row level lock of sql server, but someone tell me sql server will do performance turning itself, so it will proberbly use a table level lock sometime.
All I want to make sure is if we can let the web user never feel blocked when he is creating an account.
it's hard for me to describe it well, but I really appreciate your helps!
thanks
-
I don't believe that there will be any blocking, but I can't be sure. I believe that SQL Server will get row level locks and will let you insert. I would see if there is a way to test it.
If your UI is using .Net framework 2.0 and you don't need to give feedback to the user when the insert is completed (this is chancy), you could execute your insert assynchronously. The insert will finish when it finishes and your ui can move forward.
Russel Loski, MCSE Business Intelligence, Data Platform
-
It depends on how long the transaction from your insert will take.
If it is short, I think you'll not notice the blocking when a user that is in your list tries to insert a new row into account.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
thanks for your response!
do you mean if my transaction (select and update) last for 2 seconds,
and the web user create another account at the same time, he will proberbly blocked for 2 seconds at most, right?
i.e. the transaction got the excluded lock of the account table, right?
then, how about only select, without any update? any difference?
-
If you have a clustered index on you account-table and that index is composed of UserId, AccountId , then pagesplits may occur when another account is created.
If you clustering index is created for a growing column (e.g. identity column or datetime ) this would not cause an issue because when the last page is locked, it simply allocates a newone.
Index-contention may occur if you have an index composed of (UserId, AccountId ) because of leaflevel maintenance.
If you expect high pagesplit-chances, you can play around with pad_index and fillfactor,.. so you already reserve some space inbetween active pages and acive pages get ony filled up to the fillfactor% so it leaves some space for later update(s).
If you don't have a clustered index, your data sits i a heap. Data is inserted in an "active and available" page.
Should you throw clustered indexes away ? NO ! They support query-operations - most of the time - in a better way than heaps.
With clustered indexes, the tradeoff is - pagesplits vs hotspots - at insert-time. At updatetime it will be your fillfactor that may be decisive for pagesplit-control.
but maybe this whole philosophy may get you way off road right now
To start with, it is a good thing you're considering performance at designtime of your application/datamodel.
And like with most things, keeping track of your performance builds your knowlage of the systems behaviour and your'll learn some tricks that may keep your out of troubles for a while.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Thank you, I really appreciate your comments!
for my account table, the primary key is accountId, which is an auto increment integer (I guess sql server will create index for primary key)
and we may also create index on column userId for that we may:
select * from account where userId = ???
and I'm just wondering:
1. what is the default lock level of sql server? row level or page level.
2. will sql server auto escalate the lock level in some case? is there any chance to a table level lock?
-
1. what is the default lock level of sql server? row level.
2. will sql server auto escalate the lock level in some case? YES
is there any chance to a table level lock? YES
Is this of any concern ? If it happens .... yes
In your case, I don't think this will happen, because that would mean that a single userid would hold the majority of rows in your table, since you handle a userid per transaction.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply