Author |
Topic  |
HuwR
Forum Admin
    
United Kingdom
20587 Posts |
Posted - 21 December 2000 : 08:47:17
|
<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote> work mule:
1) transactions don't work in mysql. 2) <hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>
Why put it in a transaction ? Do you generally have problems with queries failing?
Even if this code failed, all that would happen is that either you would end up with no moderators, or only some of them, it doesn't appear critical that you should transaction it.
if it failed while adding 200 moderators, would you want it to roll it back, or leave you with half of them inserted? <font color=blue>'Resistance is futile'</font id=blue>
Edited by - Huwr on 21 December 2000 08:48:39
I just tried this against Access using Jet4, and SQL server, worked on both even with transaction left in
Edited by - Huwr on 21 December 2000 09:09:52 |
 |
|
tilttek
Junior Member
 
Canada
333 Posts |
Posted - 21 December 2000 : 10:06:18
|
<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote> Why put it in a transaction ? Do you generally have problems with queries failing? <hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>
It's more a case of work load... Queries failing? Hum, no when I have 10 users on the board, but for myself I'm use to work with BIG systems (CN, Bombardier, IBM, Banks, gouvernements sites...), so yes we need Transaction... Try to have 200 concurents user, you will see, queries failing.
<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote> Even if this code failed, all that would happen is that either you would end up with no moderators, or only some of them, it doesn't appear critical that you should transaction it. if it failed while adding 200 moderators, would you want it to roll it back, or leave you with half of them inserted? <hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>
To thing... Without Transaction you will have to do it one by one, because if you have a moderator on-line it could corrupt you database. Let say is correcting someone mail... What will hapen if he's not a moderator (for a short while) when he saving the message... Or moving the tread to a new categorie?
With transaction you could do someting like this: delete all Moderator, Insert new moderator with one single statement : insert ... WHERE Id In [1,2,5...]...
Then if the transaction didn't work, the program could pop a message to the admin, (the SQL server would roolback at the sametime), then send the admin BACK to the modification, and he could resubmit it.
Philippe Gamache http://www.tilttek.com http://www.lapageamelkor.com |
 |
|
HuwR
Forum Admin
    
United Kingdom
20587 Posts |
Posted - 21 December 2000 : 10:29:39
|
<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote> To thing... Without Transaction you will have to do it one by one, because if you have a moderator on-line it could corrupt you database. Let say is correcting someone mail... What will hapen if he's not a moderator (for a short while) when he saving the message... Or moving the tread to a new categorie? <hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>
I don't see this would corrupt anything, it either wouldn't post the changes (because not moderator) or he wouldn't see any moderator icons, changing his status halfway through a post won't make any difference.
Your code IS doing the inserts one by one.
<font color=blue>'Resistance is futile'</font id=blue> |
 |
|
work mule
Senior Member
   
USA
1358 Posts |
Posted - 21 December 2000 : 10:57:31
|
Okay....
How about using the batch method? That way everything is batched together and sent to the server once.
For every checkbox that is checked, an insert is made, so how I understand this, with the batch method it would be combined into one call to the database, whereas without using the batch method, it would have to send the data to the database for every insert (checked checkbox).
What I can't find an answer to is if transactions also batch everything and sends it once too?
Edited by - work mule on 21 December 2000 10:59:25 |
 |
|
tilttek
Junior Member
 
Canada
333 Posts |
Posted - 21 December 2000 : 11:04:27
|
<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote> I don't see this would corrupt anything, it either wouldn't post the changes (because not moderator) or he wouldn't see any moderator icons, changing his status halfway through a post won't make any difference.
Your code IS doing the inserts one by one. <hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>
Hum, I don't know who you implemented the security, so maybe it won't cause problem, but here is many thing that could create problem... Maybe not it THIS case, maybe not with mederator, but when you write one multiple tables in the forum. I might (if I have time) take a look into the forum to see where there it may cause problem.
Yes is one by one... But on one call, it a litle bit faster. With trafic we have to think of this. Because this site is good, but when you have many users at the same times, there is this that slow it very mush.
Working for site with 1000's of user by hours on it, I saw so many problem. And because I did some stress testing on many site, I can say it will cause problem.
I might do some stress testing. Don't forgot, maybe it will not be the DB that will cause trouble, but the ASP page itself, ADO, or juste the connection... For myself SQL server isn't on the same server... So the connection could be lost.
Philippe Gamache http://www.tilttek.com http://www.lapageamelkor.com |
 |
|
HuwR
Forum Admin
    
United Kingdom
20587 Posts |
Posted - 21 December 2000 : 11:12:42
|
A transaction in SQL flags the SQL code with an ID so that it can roll them back, it doesn't batch them and then send it all at once. each execute is sending an insert to the DB.
If you are worried about SQL failures, surely it is better to send them one at a time, less chance of failure because small insert, you could trap the errors and record a log so you know which ones failed. If you put them in a transaction they will all fail.
All that batching is doing is sending all the inserts togther rather than one at a time.
<font color=blue>'Resistance is futile'</font id=blue> |
 |
|
HuwR
Forum Admin
    
United Kingdom
20587 Posts |
Posted - 21 December 2000 : 11:22:08
|
using UpdateBatch would have an adverse effect on Access DB's
The UpdateBatch method of an ADO recordset can be used to send multiple recordset updates to the server in one call and can significantly reduce process times for some recordset updates. However, this is not the case with Jet databases. When the UpdateBatch method is used with a Jet database (.mdb), Jet still processes each instruction individually therefore, process times using the UpdateBatch method will generally have an opposite adverse affect of actually increasing process times by more than 100 percent.
<font color=blue>'Resistance is futile'</font id=blue> |
 |
|
work mule
Senior Member
   
USA
1358 Posts |
Posted - 21 December 2000 : 11:23:12
|
Cool! Thanks for the information! I'm definitely learning a lot of stuff through Snitz (this forum, members, and the forum code) - Thanks!! <img src=icon_smile.gif border=0 align=middle>
So...
I haven't changed the file yet. Any recommendations? Should I make two versions? <img src=icon_smile_wink.gif border=0 align=middle> Or code one of these in and then comment out the alternate version and put comments in the code?
1 - Transaction Method - the way it is now? 2 - The Batch Method - is this compatible with mySQL? 3 - or....if need be neither of the above and multiple inserts
|
 |
|
work mule
Senior Member
   
USA
1358 Posts |
Posted - 21 December 2000 : 11:31:10
|
Man, I just can't win here!! So how about checking for the db type and using a select statement for the update portion?
SQL Server - Transactions or batch mySQL & Access - multiple inserts
<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote> using UpdateBatch would have an adverse effect on Access DB's <hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>
I tested this with Access, but ya know, since it was only a test site, I only have a couple of topics and members so I wouldn't have noticed anything.
|
 |
|
HuwR
Forum Admin
    
United Kingdom
20587 Posts |
Posted - 21 December 2000 : 11:34:22
|
Just do a check for dbType and code accordingly, thatway it will work with everything, I will check on mySQl see what it supports in the way of transactions or batches.
<font color=blue>'Resistance is futile'</font id=blue> |
 |
|
tilttek
Junior Member
 
Canada
333 Posts |
Posted - 21 December 2000 : 12:08:10
|
<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote> If you are worried about SQL failures, surely it is better to send them one at a time, less chance of failure because small insert, you could trap the errors and record a log so you know which ones failed. If you put them in a transaction they will all fail. <hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>
I don't talk doing it by batch...
Ok, it's a mater of opinion. But, SQL isn't the only point of failures. For multiple table doing it, even by loging error, it may cause some coruption, let say you have to update 3 differents tables, and they ALL have to be updated, you update the 1st one, the ASP fails... You have only one updated, you don't have any log (the ASP fails). So... BIG problem.
Ok, you create small insert... 2-3 might be ok. But imaging having to do 50-100 for 100 users at the SAME time... You see my point here. Even 2-3 with many user (100+) might cause problem.
Create to many process on you server, lost time not just because creating all this but you lose CPU time just with process swaping. You might NEVER have problem with you site, but it's why I might not be able to use snitz... Requirement for site with million on hits by months.
Even the ASP cause problem, too much mode switching (<% %>). It take time. All good book on ASP tels about uptimising your code. Then there is the HTML... Yes IE is very forgiving.... Netscape isn't as much, but is forgiving. But with well form HTML will display much faster on both browser.
It might not be very easy to see, because you have only fraction of second, but, with many users all thoses fractions become seconds and maybe minutes. I yes I saw this often.
Philippe Gamache http://www.tilttek.com http://www.lapageamelkor.com |
 |
|
tilttek
Junior Member
 
Canada
333 Posts |
Posted - 21 December 2000 : 12:11:32
|
<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote> tilttek, I cannot put it into my snitz forums addons :( the asp is screwed up in some way I cannot fix. I will have to rewrite that entire script. <hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>
I just saw you answer... Hum, what is the problem? I could look at it this week-end (30 min max, but usualy I can find problem very fast).
Philippe Gamache http://www.tilttek.com http://www.lapageamelkor.com |
 |
|
HuwR
Forum Admin
    
United Kingdom
20587 Posts |
Posted - 21 December 2000 : 12:31:05
|
I wasn't suggesting that you shouldn't use transactions, you just need to be aware that for your code to be compatible with other Snitz copies, you have to think about Access and MySQL.
<font color=blue>'Resistance is futile'</font id=blue> |
 |
|
frankie
Junior Member
 
Vatican City
304 Posts |
Posted - 21 December 2000 : 12:49:38
|
Guys,
This may sound stupid, but how do you grant
a member a moderator or an admin status
without doing so in the database table?!!<img src=icon_smile_blush.gif border=0 align=middle>
ô¿~ |
 |
|
tilttek
Junior Member
 
Canada
333 Posts |
Posted - 21 December 2000 : 12:56:35
|
<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote> I wasn't suggesting that you shouldn't use transactions, you just need to be aware that for your code to be compatible with other Snitz copies, you have to think about Access and MySQL. <hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>
Exactly, so what I did suggest at first (for user using ASP 3) is to add ASP side transactions... Not SQL side!
Philippe Gamache http://www.tilttek.com http://www.lapageamelkor.com |
 |
|
Topic  |
|
|
|