This thread looks to be a little on the old side and therefore may no longer be relevant. Please see if there is a newer thread on the subject and ensure you're using the most recent build of any software if your question regards a particular product.
This thread has been locked and is no longer accepting new posts, if you have a question regarding this topic please email us at support@mindscape.co.nz
|
Hi all, I'm having a bit of a problem and could a hand. We're sending SMS messages from our system to users. SMS messages require a unique ID of an integer to track them from our system to the end phones. I don't want to make the PK of the table an integer as the rest of our tables are GUID keys, and we anticipate eventually hitting Integer.MAX, then rolling back to 0 for SMS Ids. Our system is expanding to a multiple node environment, so I can no longer use a mutex in my service and a regular table row to select the next SMS ID. This leaves me with 2 options. 1. Keep our existing SMS ID table, and somehow perform a lock on select so that no other record can read the ID while one of the nodes is incrementing it by 1.
2. Change the SmsId column to use a keytable (preferred) and give it an identityBlockSize of 10 or more.
Is 2 possible?
Current smsId table: id uniqueidentifier, smsid int Current SMS table: id uniqueidentifier, from varchar(20), to varchar(20), smsid int, message varchar(255)
Thanks, Todd
Thanks, Todd |
|
|
No, there's no built-in way to use KeyTable to populate a field other than the primary key -- interesting idea though! Wrapping the "select and increment" operation in a stored procedure (and denying direct table access permissions to the SMS ID table) might be the way to go here -- I am assuming that you don't actually need the SMS IDs as entities despite the SMS ID table having a PK. |
|
|
You're correct Ivan. The guid field was only there due to the LS requirement. Do you have any examples/documentation links on how to only allow selects through a stored procedure? I'm comfortable with the stored procedure bit, but I can't seem to find much documentation on disallowing update on all users except for via a stored procedure. I'd still like to be able to perform a select query for debugging purposes.
Thanks, Todd |
|
|
Setting up permissions will be database specific -- and optional! I only suggested it so that if a year from now some bright spark decides to perform updates and increments against smsid from their own code, not realising that locking behaviour is required, they'd get an error and be forced to go through the safe sproc. The idea would be that you would deny select, insert, update and delete permission on the smsid table to the user account associated with your production site, but grant them to your maintenance login (so you could still do your debugging queries). The production site account would have only permission to execute the sproc. But as I say this is just an optional safety suggestion -- you don't need to do it to enable sproc-based access -- and you may reasonably deem it not worth the effort of figuring out! If you do want to do it then you would use SQL Server Management Studio, MySQL Administrator, etc. E.g. in SQL Server right click the table, choose Properties and select the Permissions tab. |
|