August 30, 2006 at 7:12 am
I am trying to figure out how to force our users to use transactions from within Query Analyzer. I see that there is an option to set implicit transactions under the Query\Current Connection Properties menu and the Tools\Options\Connection Properties, but am unable to find any doc that indicates whether these affect only the end users' current session or all connections. I need the users who use QA to use transactions, but I don't want to force any other connections to do so.
Thanks!
August 30, 2006 at 7:15 am
You want to do that in case they accidentally delete/update some important data??
If this is the case, then I really suggest you set up a test environnement where they can screw up all they want. Also you might want to learn how to do backups and restores.
August 30, 2006 at 7:20 am
Unfortunately, restoring from backup and fixing data is time and resource intensive (expensive). While we do have a backup/restore strategy, we'd also like to do what we can to limit the need to use that strategy. Forcing the use of transactions is just one part of that.
August 30, 2006 at 7:30 am
I've never heard of anything like that... the only thing I can suggest is that they use templates which contain the begin tran statement... but other than that I don't see how you cna force it upon 'em...
August 30, 2006 at 7:44 am
Hi Jon,
Like the previous poster I'm not sure what it is you want to achieve here.
Firstly, by default, all update/delete/insert statements that users run automically run as transactions. You don't need to do anything. SQL Server will autocommit these statements for you.
You could turn this off by using the SET IMPLICIT_TRANSACTIONS option but trust me, you do not want to do that. With this option, SQL Server will begin a transaction but it will not commit it. In other words, users will need to remember to manually type out COMMIT TRAN.
Now imagine that a user forgets to type out COMMIT TRAN. Your job will become a nightmare because all of a sudden you'll have lots of blocking (since there are open transactions) and you'll have a high number of open transactions in the database that haven't been committed.
Let us say though that your users are good users and they all remember to type in COMMIT TRAN.
If that's the case, you might as well be running with IMPLICIT_TRANSACTIONS OFF. And if the users are that good that they remember to commit transactions then ask them to manually type begin tran ... commit tran statements around each update they perform.
As the previous poster suggested, if you're that worried about the users screwing up the data, stop them from updating the data and keep backups. Or give them a database on their workstation that they can play with.
August 30, 2006 at 9:52 am
First, thanks to everyone for offering suggestions!
We are trying to implement a policy that we think will minimize the necessity for data recovery. Recovery is expensive because the entire process takes so much time and resource. We aren't doing logging because of the performance hit so we can only recover from the backup file. There is one file group, so we'd have to restore the entire db to recover data. So, we are looking at several hours to a day to restore the db.
We have about 6 users that have a business need to make corrections to some of the data in our operational DW. So, we are contemplating giving them update access. We do want them to have to issue begin tran prior to any updates, deletes, and inserts. But, we have found that sometimes the users cut corners or become complacent and issue the commands without the transaction. So, if we could force implicit transactions, this seems to be ideal as it takes away the burden of remembering to issue the begin tran.
We understand that this may cause locks, but those are pretty easy to diagnose and the users can run sp_who2 to check. We are also implementing a certification process for gaining the update access and expect users to know what they are doing. Failure to do so will revoke privileges.
So, I was hoping to find a way to force the use of implicit transactions for a particular session. If the scope of SET IMPLICIT_TRANSACTIONS is the session that issued it, I think we are OK. However, I am concerned that running this will set this for all connections to the db. I am also concerned that if the implicit transactions are enabled and there are any pending transactions when the session is ended (QA is closed) they will be auto-commited (or do they get rolled back?).
August 30, 2006 at 10:08 am
I think I'd be more concerned with the performance problems caused by blocking locks than those caused by backups.
Have you considered having the users use an application that calls stored procedures to do the updates? We try to avoid giving users direct update permissions.
Greg
Greg
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply