do not set any locking\blocking

Question

do not set any locking\blocking

Mad-Dog

SSCertifiable

Points: 6117
More actions
May 27, 2016 at 2:30 am

#326612

Hi,
is there away to set database to not issue any locks/blocks on all tables?
i don't want that insert will block select and insert will block another insert and delete statement.
i'm not afraid of dirty reads etc..
THX

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

There is no way to do that in a relational database.

I would rather focus on the reason why you're getting blocked, find the root cause and fix it. Can you describe your scenario in more detail?

-- Gianluca Sartori

Mad-Dog SSCertifiable Points: 6117 More actions · Answer 2

spaghettidba (5/27/2016)
There is no way to do that in a relational database.
I would rather focus on the reason why you're getting blocked, find the root cause and fix it. Can you describe your scenario in more detail?

OK 🙁

well the application we use have a specific database that store rows for manipulation when they run queries. this is a shared tables for all users.

the rows that the user insert to those tables are not shared by any other user (select/insert/update). it's just like that you use the tempdb for every user session to handle his query.

an example - user insert 300,000 rows for manipulation during the insert other users blocked for insert or select or delete or update until his insert is finished, and when he finished his manipulation the program delete those 300,000 rows and then we get locks again until the delete finished..

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

You really wouldn't want no locks at all. Rows deleted while they're still being inserted, updates affecting some of the rows they should because the rest are still being inserted, etc, etc, etc.

For your shared 'temp' table, that's often a bad idea for exactly the reasons you're seeing. May I suggest you look at redesigning it so that the users use temp tables, or give every user their own schema with their own table that they can insert, update, delete at their leisure.

Alternately look at how the table is indexed and make sure that SQL isn't having to scan the entire table to find User7's rows.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Mad-Dog SSCertifiable Points: 6117 More actions · Answer 4

GilaMonster (5/27/2016)
You really wouldn't want no locks at all. Rows deleted while they're still being inserted, updates affecting some of the rows they should because the rest are still being inserted, etc, etc, etc.
For your shared 'temp' table, that's often a bad idea for exactly the reasons you're seeing. May I suggest you look at redesigning it so that the users use temp tables, or give every user their own schema with their own table that they can insert, update, delete at their leisure.
Alternately look at how the table is indexed and make sure that SQL isn't having to scan the entire table to find User7's rows.

the problem is that we cannot do any thing here this is how the program work and we can't interact with the queried to put no lock etc.. it's closed program!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Have you tried read committed snapshot? Won't help with deletes blocking inserts, but will help with reads.

And have you checked the indexing to make sure it supports the queries? Poor indexing often results in blocking problems.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Mad-Dog (5/27/2016)
GilaMonster (5/27/2016)
You really wouldn't want no locks at all. Rows deleted while they're still being inserted, updates affecting some of the rows they should because the rest are still being inserted, etc, etc, etc.
For your shared 'temp' table, that's often a bad idea for exactly the reasons you're seeing. May I suggest you look at redesigning it so that the users use temp tables, or give every user their own schema with their own table that they can insert, update, delete at their leisure.
Alternately look at how the table is indexed and make sure that SQL isn't having to scan the entire table to find User7's rows.
the problem is that we cannot do any thing here this is how the program work and we can't interact with the queried to put no lock etc.. it's closed program!

I am curious about what kind of program this is.

Mad-Dog SSCertifiable Points: 6117 More actions · Answer 7

Lynn Pettis (5/27/2016)
Mad-Dog (5/27/2016)
GilaMonster (5/27/2016)
You really wouldn't want no locks at all. Rows deleted while they're still being inserted, updates affecting some of the rows they should because the rest are still being inserted, etc, etc, etc.
For your shared 'temp' table, that's often a bad idea for exactly the reasons you're seeing. May I suggest you look at redesigning it so that the users use temp tables, or give every user their own schema with their own table that they can insert, update, delete at their leisure.
Alternately look at how the table is indexed and make sure that SQL isn't having to scan the entire table to find User7's rows.
the problem is that we cannot do any thing here this is how the program work and we can't interact with the queried to put no lock etc.. it's closed program!
I am curious about what kind of program this is.

the program called Priority

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Mad-Dog (5/27/2016)
Lynn Pettis (5/27/2016)
Mad-Dog (5/27/2016)
GilaMonster (5/27/2016)
You really wouldn't want no locks at all. Rows deleted while they're still being inserted, updates affecting some of the rows they should because the rest are still being inserted, etc, etc, etc.
For your shared 'temp' table, that's often a bad idea for exactly the reasons you're seeing. May I suggest you look at redesigning it so that the users use temp tables, or give every user their own schema with their own table that they can insert, update, delete at their leisure.
Alternately look at how the table is indexed and make sure that SQL isn't having to scan the entire table to find User7's rows.
the problem is that we cannot do any thing here this is how the program work and we can't interact with the queried to put no lock etc.. it's closed program!
I am curious about what kind of program this is.
the program called Priority

Who sells it? Just doing a Google search doesn't come up with anything in the first page that even looks like an application program.

Mad-Dog SSCertifiable Points: 6117 More actions · Answer 9

Lynn Pettis (5/27/2016)
Mad-Dog (5/27/2016)
Lynn Pettis (5/27/2016)
Mad-Dog (5/27/2016)
GilaMonster (5/27/2016)
You really wouldn't want no locks at all. Rows deleted while they're still being inserted, updates affecting some of the rows they should because the rest are still being inserted, etc, etc, etc.
For your shared 'temp' table, that's often a bad idea for exactly the reasons you're seeing. May I suggest you look at redesigning it so that the users use temp tables, or give every user their own schema with their own table that they can insert, update, delete at their leisure.
Alternately look at how the table is indexed and make sure that SQL isn't having to scan the entire table to find User7's rows.
the problem is that we cannot do any thing here this is how the program work and we can't interact with the queried to put no lock etc.. it's closed program!
I am curious about what kind of program this is.
the program called Priority
Who sells it? Just doing a Google search doesn't come up with anything in the first page that even looks like an application program.

it's an erp software.

http://www.priority-software.com

Eddie Wuerch SSChampion Points: 12532 More actions · Answer 10

If you have a support agreement, call them up and scream at them.

Eddie Wuerch
MCM: SQL

Mad-Dog SSCertifiable Points: 6117 More actions · Answer 11

if i setup in the index page lock = false and row lock = false will it help reduce the locks on those tables?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

Mad-Dog (7/31/2016)
if i setup in the index page lock = false and row lock = false will it help reduce the locks on those tables?

Reduce the number, yes. Reduce the impact, no. With those two false, SQL has to take table-level locks.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 13

Eddie Wuerch (5/27/2016)
If you have a support agreement, call them up and scream at them.

+1000 to this. Call the software provider up and show them what's going on with their software and have them fix it. Remembering that they caused the perf problems in the first place, be prepared to show them both the cause and a recommended fix if you want to continue to use their software.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Mad-Dog SSCertifiable Points: 6117 More actions · Answer 14

they know about the problem and for now they can't fix the problem.

table scan is not good because some tables can have even millions rows for they temp table.

what i can set to false that will reduce the locking and wouldn't do table scan?

Thanks