September 21, 2012 at 9:59 am
GilaMonster (9/21/2012)
If it is a table lock, it's probably because you don't have an index on the Product column.Tune indexes, optimise code, then and only then try to outsmart the lock manager if you know exactly what you're doing and why.
And no, there is no way to force a row lock. You can ask for locks to start at row (which for queries that affect small amounts of data they probably will anyway), but that does not prevent SQL escalating the locks if if feels the need to do so.
Dohhh... my stupid mistake...
I was making some tests and put the table hint "WITH (TABLOCK)" on the 2nd window...
It wouldn't update since it couldn't get a table lock...
Sorry 🙁
Is that a question or a statement?
Statement... But since I was asked if I knew what it is I was doubting myself...
Thanks,
Pedro
September 21, 2012 at 10:15 am
PiMané (9/21/2012)
Lynn Pettis (9/21/2012)
First question I have, how do you know it is a table lock?Entire table and indexes locked?!
Isn't there a way to lock only a row or data page so records on other rows or data pages can be updated?
Thanks,
Pedro
This really didn't answer the question any way. After reading your later post about hove the tablock hint on the query, makes sense that it had to wait.
My question, however, was asking how you knew it was table lock blocking the second update.
September 21, 2012 at 10:19 am
Lynn Pettis (9/21/2012)
My question, however, was asking how you knew it was table lock blocking the second update.
yep... I was "forcing" it 🙂
Mess cleaned.. thanks.
September 21, 2012 at 10:33 am
PiMané (9/21/2012)
Lynn Pettis (9/21/2012)
My question, however, was asking how you knew it was table lock blocking the second update.yep... I was "forcing" it 🙂
Mess cleaned.. thanks.
I understood that you were forcing it, but at the time I asked the question this was not known. I was simply trying to explain better that my original question was asking you how, at the time and not knowing you used with (tablock) on the 2nd query, knew there was a table lock blocking the second update. We now know that the second update was trying to acquire a table lock and couldn't until you commited the first transaction.
September 21, 2012 at 10:37 am
Lynn Pettis (9/21/2012)
I understood that you were forcing it, but at the time I asked the question this was not known. I was simply trying to explain better that my original question was asking you how, at the time and not knowing you used with (tablock) on the 2nd query, knew there was a table lock blocking the second update. We now know that the second update was trying to acquire a table lock and couldn't until you commited the first transaction.
I know, thanks for all the help so far 🙂
Even if the database isn't with RCIS it works fine.
Now I just have to write the document, "sniff" whatever developers will write and clean old messes...
Thanks,
Pedro
September 21, 2012 at 3:16 pm
PiMané (9/21/2012)
Aren't FKs faster to check than the IF SELECT .. ? If we name our FKs we can catch the specific error on code and display a more user-friendly error message..
Perhaps, but you've still opened, at the least, a row-level exclusive lock that was unnecessary and something that could have been tested beforehand without serious interruption of the rest of the OLTP system (such as shared table lock escalation) until the transaction rolls back.
As I mentioned, I'd really only do it for common errors, though. Most FK fields are drop-boxes instead of free-type so it's not something I'd usually concern myself with unless they were external data loads.
Yep, that the main goal.. but if they can't be avoided is it better to insert the "cursor" data on a cursor LOCAL FORWARD_ONLY FAST_FORWARD or on a table variable and loop it?!
As mentioned above, you'll need to test for which is better. Cursors can offer easier functionality to the exact same task however, with little difference. Loop is loop. Cursor overhead is to be avoided primarily because of the hidden locking it performs. With proper expertise you can perform the same tasks equivalently, which most new coders don't have so they end up with a mess, thus the common practice of 'kill all cursors'.
This is something I read on some blog. It says that if you have IF statements and declare the variables as you need (inside the IF) the query plans change according to the IF condition and doesn't reuse them as much... don't know if its true but...
Read the article linked by Gail when you get a chance. There are different patterns to the cache'd plan construction, but the location of the variable declaration isn't one of them. It DOES however make your life a ton easier if they're all declared in a single place.
This is an old concern from previous versions of SQL Server (around 7.0). SELECT * and SELECT 1 for IF EXISTS checks function equivalently. I don't disagree with breaking the habit of SELECT * from developers, but just as a side note.
LOB stuff: Gail already pointed out the issues with your confusion about memory usage.
In general, most LOB stuff should never be stored in the database. There is only one reason that it's typically a 'good' idea to store LOB data in the database, and that's when your data and the LOB data needs to be in lockstep on a restore. Otherwise, it's typically best to store a path to the file being saved and allow the application software to handle the rest directly from the filesystem.
Our most heavy OLTP operation is inserting a sales document... It has triggers with cursors that call SPs to update the customers accounting, change the products stocks, ... It has many tables operations, and take a while to process. RCSI OR READ UNCOMMITTED can be a solution for reading data but I think I'll have a problem when 10 people try to save a sales document at the same time... Does RCSI make lock on the rows or table? With our current system (I'm not sure what it is cause I haven't analysed it deeply, but think is the default lock) when I insert a sales document with product A , B and C to customer C1 and someone else inserts a sales document with product X,Y and Z to customer C2 it was to wait, the locks are made on the entire table... Is there any system to make a row lock only?
I'd bring this to another thread where we can review the code, the schema, and the existing .sqlplans (take a look at the links in my signature for help with indexes/tuning for what we'd need) and can clean up your code. This doesn't sound like a problem with standards or locking, it sounds like the design from the ground up could use a cleanup/review. Cursor called procs are notoriously bad juju unless done for very particular reasons on incredibly large systems... which isn't the case if your clients aren't even running RAID.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 21, 2012 at 4:11 pm
Evil Kraig F (9/21/2012)
I'd bring this to another thread where we can review the code, the schema, and the existing .sqlplans (take a look at the links in my signature for help with indexes/tuning for what we'd need) and can clean up your code. This doesn't sound like a problem with standards or locking, it sounds like the design from the ground up could use a cleanup/review. Cursor called procs are notoriously bad juju unless done for very particular reasons on incredibly large systems... which isn't the case if your clients aren't even running RAID.
Thanks for all the advises.
When I get "deep" in the database and start analyzing the code and structure I'll surely be posting some threads... Wish I hadn't but by what I've seen just in a glace I'm going to have a LOT of work...
At least my work is assured for quite a long time...
Thanks,
Pedro
Viewing 7 posts - 31 through 37 (of 37 total)
You must be logged in to reply to this topic. Login to reply