Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
eric@intapp.com
Starting Member
2 Posts |
 Posted - 2007-04-23 : 17:23:46
|
| I have multiple applications which can potentially update the same trigger simultaneously. Each application:(1) Reads the contents of the current trigger(2) Creates a new trigger based on the current contents(3) Drops the trigger(4) Creates the new triggerI need to insure that once one application begins step (1), then no other application can start step (1) until step (4) has completed.Any ideas on how this can be done? Some databases have a concept of locking tables explicitly, so for them, I can do:(0) lock table foo;(1) - (4)(5) unlock table foo;Is there such a concept in mysql or is there another way of accomplishing this?Thanks,Eric |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-04-23 : 17:43:12
|
| I dont understand what you mean by 'trigger' here? is it a name of a table? you might want to look into using Transactions in SQL Server. If you are using stored procs, you could use BEGIN TRAN/COMMIT/ROLLBACK/END TRAN in them. check out BOL for more info on how to use TRANSACTIONs.************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
MutantNinjaLoungeSinger
Starting Member
14 Posts |
Posted - 2007-04-23 : 18:26:19
|
quote:
Is there such a concept in mysql or is there another way of accomplishing this?
This forum is specific to SQL Server.However to answer your question, MySQL performs table locks on queries whether you want them to or not if the table is a MyISAM table. If you want to lock tables for the duration of a transaction with myISAM, you can do as you've mentioned (the lock...unlock script), or do an application level lock on some key that pertains to your grouping of records.The record-locking version of table storage (the name escapes me..InnoDB?) follows a pattern similar to dinakar has mentioned.. begin/commit/rollback... If you have a significant number of records in your tables, this would be the best way to go. |
|
|
|
eric@intapp.com
Starting Member
2 Posts |
Posted - 2007-04-23 : 19:54:46
|
| Thanks for the quick responses!There was a typo in my initial question. I meant to say SQL Server, when I mentioned MySQL. It is meant to be a SQL Server question. Although incidentally, in MySQL I can do what I need with: lock tables... and unlock tables.As for what I mean by trigger, I want to create a table trigger. So, to be more explicit about the above steps:(1) Reads the contents of the current trigger for a table(2) Drops the trigger whose contents were read in step (1), i.e. DROP TRIGGER ...(3) Creates the new trigger, i.e. CREATE TRIGGER ...Thanks!- Eric |
|
|
|
cvraghu
Posting Yak Master
187 Posts |
Posted - 2007-04-23 : 20:04:35
|
| You may have to write your own queuing logic. Create a table with a column to be used as flag. 1. Any process getting into step 1 should check whether the flag value is 0 and proceed. Otherwise wait in a loop.2. Any process, once it gets into step 1 should update the flag to 1.3. Any process which leaves step 4 should update the flag back to 0.Just my thoughts. |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-04-23 : 22:39:22
|
Your application should not be adding and dropping triggers. If you think they do, the design is flawed somewhere. Application code should be DML only, not DDL. if the schema is changing at runtime, you are setting yourself up for many troubles.The only exception to this rule I can think of is if you are writing an app to manage DBMS themselves, such as SSMS or TOAD.
www.elsasoft.org |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2007-04-23 : 22:56:17
|
| This is soooooo wrong. You will be creating a huge performance problem (locks are bad for concurrency, table locks are the worst). You will also have a good likelihood of stuffing up your transactions in all manner of ways (can't quite remember if changing the trigger would commit your transactions - it's not something I'd ever do so I'm not quite up to speed on mixing schema stuff with data stuff).I can't even begin to think what's going on here but I would urge you to reconsider your design. I'm sure if you outlined what you are trying to do there are better ways than this and there are plenty of people here who could help.Just in case I didn't make myself clear - DO NOT DO THIS!!! AAARRGG!! |
|
|
|
|
|
|
|
|
Explicitly lock a table?