Can we just lock the schema rather a DB?

Question

Can we just lock the schema rather a DB?

srinivas.akyana

Ten Centuries

Points: 1269
More actions
June 1, 2016 at 4:20 am

#308149

Hello - we do lock the DB during the processing to avoid any processing glitches. And, I am wondering if we can just lock the schema rather locking the entire DB?
For example, Public is the schema in a database Staging and I would like to lock only "Public" schema rather an entire DB during processing so other schema's on this DB won't be impacted.
Please help
Thanks!

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

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

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

Err, SQL doesn't have a built in way to 'lock' a database, so it must be something that you've built. Can you explain how you 'lock' databases?

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

Steve Jones - SSC Editor SSC Guru Points: 736129 More actions · Answer 2

SQL Server takes locks as needed to process data, change objects, etc., to avoid glitches. As Gilamonster mentioned, there isn't a lock db or schema option I'm aware of.

Jeff Moden SSC Guru Points: 1004304 More actions · Answer 3

Steve Jones - SSC Editor (6/1/2016)
SQL Server takes locks as needed to process data, change objects, etc., to avoid glitches. As Gilamonster mentioned, there isn't a lock db or schema option I'm aware of.

There kind of is... setting the database to Single User will do it. Of course, good luck when the session accidently loses connection and one of the Web Servers locks onto the system. 😉

--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)

Steve Jones - SSC Editor SSC Guru Points: 736129 More actions · Answer 4

Not sure that's a lock. It's a login/use restriction, maybe technically a lock, but you haven't used any locking to process data there. Certainly a process dropping off allows another to take over, without necessarily doing a commit/rollback of work happening.

Jeff Moden SSC Guru Points: 1004304 More actions · Answer 5

Steve Jones - SSC Editor (6/1/2016)
Not sure that's a lock. It's a login/use restriction, maybe technically a lock, but you haven't used any locking to process data there. Certainly a process dropping off allows another to take over, without necessarily doing a commit/rollback of work happening.

Agreed. It's not really a lock. It's a restriction. It does have a similar effect for a single user.

--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)

srinivas.akyana Ten Centuries Points: 1269 More actions · Answer 6

My apologies for the confusion. Yes, it's not a lock, indeed a restriction. Question is, can we restrict a specific schema only rather an entire DB?

Thanks!

Steve Jones - SSC Editor SSC Guru Points: 736129 More actions · Answer 7

Well, I'd want to know why, but the only way I know to do this (Easily) is use roles for all permissions and then remove permissions from a role.

If you have a schema, say ETL, I'd grant all permissions to the ETL schema to a specific role or two. Add users to those roles. Then, I can remove the users from the role, before some processing and then add them back later. I believe I'd still need to quiesce the ongoing actions against objects from users, as their permissions aren't removed if they are accessing the objects. That could be an issue no matter what.

I don't know of another way to synchronize the prevention of access to a number of objects.

There is no way to restrict users from a particular schema or set of objects I'm aware of. The whole idea of SQL Server is to try and ensure concurrency with stability in accordance with ACID principles.

IF you let us know what you're trying to achieve, maybe we have other ideas. Perhaps RCSI would help, but not sure.

Jeff Moden SSC Guru Points: 1004304 More actions · Answer 8

Steve Jones - SSC Editor (6/1/2016)
Well, I'd want to know why, but the only way I know to do this (Easily) is use roles for all permissions and then remove permissions from a role.
If you have a schema, say ETL, I'd grant all permissions to the ETL schema to a specific role or two. Add users to those roles. Then, I can remove the users from the role, before some processing and then add them back later. I believe I'd still need to quiesce the ongoing actions against objects from users, as their permissions aren't removed if they are accessing the objects. That could be an issue no matter what.
I don't know of another way to synchronize the prevention of access to a number of objects.
There is no way to restrict users from a particular schema or set of objects I'm aware of. The whole idea of SQL Server is to try and ensure concurrency with stability in accordance with ACID principles.
IF you let us know what you're trying to achieve, maybe we have other ideas. Perhaps RCSI would help, but not sure.

To give a coded example of what Steve says above, please see the "Using Schemas in SQL Server" section at the following URL.

https://technet.microsoft.com/en-us/library/dd283095(v=sql.100).aspx

--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)

srinivas.akyana Ten Centuries Points: 1269 More actions · Answer 9

Thanks much, Steve! Here's what I am trying achieve, we have a DB with couple of schemas with different user base and we restrict access during processing (current situation). However this approach doesn't seems to be quite efficient as Schema B user group is also getting restricted while Schema A processing. Hence I would like to have some process in place to restrict only Schema A user group during Schema A processing without effecting Schema B user group.

Let me know if you need any further information. Appreciate your help once again

srinivas.akyana Ten Centuries Points: 1269 More actions · Answer 10

Thanks Jeff! I will try that option and let you know as well.

Jim_K SSCrazy Points: 2418 More actions · Answer 11

Random thought: you might be able to cobble something together via sp_getapplock, but I think you would have to carefully architect and test how users access the database. Also no idea what kind of performance penalty would be associated with it either.

Lowell SSC Guru Points: 323518 More actions · Answer 12

i looked at sp_applock, and it would not give you any granularity at the object level.

I'm not sure if the OP is just second guessing SQL locking, and is afraid someone might touch the data, or if it REALLY needs to be exclusively modified.

how about this idea: rename any tables affected in your workload, do the work, and rename then back to the original names.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jim_K SSCrazy Points: 2418 More actions · Answer 13

Lowell (6/28/2016)
i looked at sp_applock, and it would not give you any granularity at the object level.

Hmm, I was thinking something like all data access through stored procedures, which would check for a created lock if they needed to be blocked during a particular process. The obvious downside is now you are creating a dependency on a single shared object which could incur overhead (how much I'm not sure). Renaming tables is an interesting approach though, would using synonyms be more advantageous?

alex.sqldba SSChampion Points: 10254 More actions · Answer 14

Could you disable the login(s) during 'processing' ?

That's what we do here to stop users logging in when we're performing a nightly bulk load - the users tend to get in the way; try and do things outside of service hours, and then complain and raise tickets that 'it was slow' -- so to avoid this and to enforce the service time, for this particular server only we just disable a handful of the logins until the process is complete.