| Author |
Topic |
|
tool
Starting Member
26 Posts |
 Posted - 2002-10-15 : 17:55:39
|
| I am working on a web application which uses timestamps to prevent concurrent updates. Up to this point I have been using a function described in the following KB article [url]http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q170380&[/url]to convert the timestamp to a string and have then been able to later pass it directly into a sproc expecting a timestamp with no problems.I am trying to change the application so that multiple updates can be included in a single transaction by passing CSV lists for each field to a sproc that then breaks them down and includes all updates in a transaction (it is discussed a bit in this post [url]http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=19846[/url]). The problem is that once I get the CSV list of timestamps into the sproc and break them apart I cannot get them converted back to the original binary values, SQL Server already thinks they are strings.I think the following code frames the problem fairly well:CREATE PROCEDURE sTest @ts timestampASSELECT @tsGOEXEC sTest 0x00000000000A0F0A --works fine, just like how it's currently called from the appEXEC sTest '0x00000000000A0F0A' --doesn't work as a string like it exists after I parse the CSVI've tried a number of different conversions but nothing is working, they are all converting the string to a binary when I really need the binary value that the string represents.Anyone have ideas? If I need to clarify anything let me know. Thanks |
|
|
tool
Starting Member
26 Posts |
Posted - 2002-10-16 : 12:05:18
|
| Has no one ran into this before? Should I be using datetime fields rather than timestamp to prevent stale updates?Any help would be appreciated! |
 |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-10-16 : 12:10:06
|
| Sounds like DateTime fields would do what you need to do. It seems like the BOL pushes you toward timestamps, but they sure don't seem to be working in this case. I've never really used either method, so I can't tell ya which is best.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2002-10-16 : 13:42:06
|
This doesn't have any error checking:CREATE FUNCTION dbo.hexstrtovarbin(@s varchar(258))RETURNS varbinary(128)ASBEGIN DECLARE @h char(16), @b varbinary(128), @l int, @i int SELECT @h = '0123456789ABCDEF', @b = SUBSTRING(0x00, 1, 0)0x, @l = LEN(@s), @i = 3 WHILE @i < @l BEGIN SET @b = @b + CAST((CHARINDEX(SUBSTRING(@s, @i, 1), @h)-1)*16 + CHARINDEX(SUBSTRING(@s, @i+1, 1), @h)-1 AS binary(1)) SET @i = @i + 2 END RETURN @bEND Edit: apparently 0x is a valid literal for the zero-length varbinary value.Edited by - Arnold Fribble on 10/16/2002 17:08:39 |
|
|
|
tool
Starting Member
26 Posts |
Posted - 2002-10-16 : 16:04:08
|
Much thanks Mr. Fribble! Because no one had an answer right away for my dilema it makes me wonder, is no one else using timestamps to prevent stale updates? I would think that any web app that updates a database would need some mechanism to do this. What approach is everyone else (web developers) out there using to accomplish this? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-16 : 16:32:33
|
| My experience with collisions has been pretty minor, and I've spent my time preventing two people from working on the same account at the same time, for example, rather than trying to deal with two people trying to update the same rows. Truthfully it never really happened that often either, and as the saying goes, an ounce of prevention is worth a pound of cure.One way I did it was to have a queue or account list that the user could select from, or have it automatically grab the next account and assign it to them. The table was keyed on account number, and the procedure would check and set a status flag to indicate whether the account was assigned or not. At the time of assignment too, the procedure would check to make sure someone didn't already grab it. Even with the account list two people almost never chose the same account at the same time, maybe 1 time out of 200 or 250 accounts. It required them to click the same account at almost literally the exact same millisecond.Another way to work around it in a web situation, if you use an account list, is to have the page automatically update every 5 minutes or so, or whatever is appropriate. That way if some moron lets the screen sit during lunch they won't accidentally choose a stale account when they get back. It's something that can be done with a bit column or character data.This was all for a call queue application, BTW, so if you're doing something like that it should work for you. It's hard to say whether it's really necessary without knowing more about your application, but I have heard a lot of developers and clients worry about collisions happening in situations where they either won't happen, or can very easily be prevented. |
|
|
|
tool
Starting Member
26 Posts |
Posted - 2002-10-16 : 17:12:30
|
| Thanks for the feedback Rob,I'm working on a financial planning app (budgeting,forecasting,etc.) in which user access is assigned at the cost/profit center level. Within each center there are a variable number of accounts, each account then contains a variable number of line items. The application is set up so a user can drill down to a specific account within a center and access (view,insert,update,delete bases on permissions) all the line items within that account.As I said access is controlled at the center level but it would be impractical to lock a center for one user becuase some centers can be and are accessed by up to 50 users. The same holds true at the account level because some accounts may contain 100+ line items being maintained by numerous different users. Adding to the likelyhood of collisions is the fact that usage of the app tends to have dinstinct peeks throughout the year like quarter end when everyone's forecast is due (and of course everyone procrastinates).I'm pretty sure I need the mechanism to prevent collisions, I am now wondering if timestamps are the best solution (although Arnold's solution seems to work just fine). I think a datetime field updated by a trigger would work just as well not to mention it would provide additional useful info (i.e. date last updated).I'd still be interested in hearing any other comments/solutions.Thanks for the help! |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2002-10-21 : 11:17:54
|
| 1 thing to be aware of...is false collisions....especially if you have batch updates, using some common "collision detection code"....(stored in a trigger?)...which is designed for solving an interactive activity problem.A batch will usually update/insert multiple records per second....and if your server is fast enough, maybe multiple records per millisecond....time fields don't go more accurate than (a fraction of??) milliseconds."Claiming" records for update may not work...if the "held for update" flag is too generous at times of peak activity....people may not be too impressed (during the busy business periods) if sets of records get locked away from them for "long" periods of time, only to be sent back with 1/2 updates out of the full set....it's a balancing act, between giving a person a useful amount of work to do and adaquate time to complete it...without causing huge backlogs for everynbody else....if little/nothing is done on the allocated work....(you could though track "claimed but un-actioned work"....and point the finger at the "user" culprits...by some smart coding....) |
|
|
|
royv
Constraint Violating Yak Guru
455 Posts |
Posted - 2002-10-21 : 11:31:08
|
I have an application that I wrote using unique datetime values instead of timestamps, and it supports around 50 users, similar to what you have stated. However, this is for session tracking of users. Therefore, since I only have 50 users, I have no issues, if I had the situation that AndrewMurphy is talking about, smart coding is definetly required... *************************Someone done told you wrong! |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-21 : 11:36:59
|
quote:
if little/nothing is done on the allocated work....(you could though track "claimed but un-actioned work"....and point the finger at the "user" culprits...by some smart coding....)
I totally agree with Andrew's assessment, especially this bit. The apps I wrote assigned accounts to users, and the users could review their queues for anything that might've gotten lost in the shuffle. They were expected to review their queues at least once a day and clean up everything within 3 days. I created a few reports to spot anybody who might've missed the deadline, and the supervisors ran these reports every day and managed the users accordingly. Less work for me!Although I must admit the code I wrote to do this was anything but "smart"... ...still, it's not hard to do at all. |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2002-10-22 : 10:55:00
|
| must agree with robvolk...."supervisors ran these reports every day and managed the users accordingly"....great additional feature....get the supervisors to manage their staff....not the data....and not YOU!I hate being spyed upon (supervised)....but for the sh1tty things in life....it's great to have someone else, prompt me to jump occasionally.....less thinking for me to do!also....by "smart programming"...and concurring with robvolk again...i didn't meant "difficult"....data structures involved can be relatived simple....it just needs a bit of thinking. |
|
|
|
|
converting to binary