WHEW!

[Robert]

First ever database corruption! Had to do a rollback on the database. The disadvantages of using MySQL, no transaction support!

Anyway, let me know if there are any errors, it should be all fixed.

Sorry for the slight downtime.


Bobby

[the slave]

What ?  When??  Have I missed something ??

OK here at 10.30am and again at 2.20pm today

[Robert]

Twas pretty early, around 8:30 or so.

It's pretty freakish, it usually occurs when two or more people try to write to the same row on a table at the same time. Transaction support and row-level locking are features that most modern DB systems have(Oracle, Postgres, MicrosoftSQL).

Row-level locking basically locks the row that a particular user is writing to, while that row is locked only one user can write to it, but others can't view it until the user is finished.

Modern DBs use Transaction Isolation Levels. (TIL)

Transaction isolation levels dictate how visible a transaction's intermediate product (the state of the data undergoing change before it is committed to the database) is to a second process that also needs to access the resources undergoing a change. The changes result from the traditional CRUD actions (Create, Read, Update, and Delete).
The four transaction isolation levels are:

Read Uncommitted Isolation Level - The benefit of Read Uncommitted is that it offers maximum concurrency; transactions don't have to wait or be blocked because no shared locks or exclusive locks are given. Under the Read Uncommitted isolation level, a second process can see data before the transaction that changes the data is complete. This is called a dirty read. Another possible con is the lost update: a change made by a first process is changed by a second one.
Read Committed Isolation Level - Read Committed guarantees that the data viewed by a transaction is "real." This level holds shared locks, which prevents dirty reads, but a writing transaction could cause either an unrepeatable read or a phantom record.
Repeatable Read Isolation Level - Repeatable Read locks the data viewed to guarantee its stability throughout the entire transaction. A second transaction can add new inserts to the set, however. If the process needs to revisit the data, it will see the same data it did the first time, but it could see additional, "phantom" data as well.
Read Serializable Isolation Level - In the Read Serializable level, the set is guaranteed to be free of phantoms because locks are held throughout the transaction. The set under consideration cannot have any updates, inserts, or deletes that weren't there the first time. The tradeoff for serialized transactions is it has the lowest concurrency. No changes can happen to the data until it is finished.
By serializing the power to change data, the state of the data is guaranteed to be consistent (i.e., stable for each modification made). In general, the four levels illustrate the trade off in transaction isolation: maximum concurrency comes at the price of data integrity.


OrthodoxChristianity.net uses MySQL, which isn't a real database, as it doesn't meat full ACID/CRUD specifications. As such we don't have all the protections that an enterprise level database management system would have.


Bobby

[David]

Well, Bobby, you know these problems would have never occured had you used Microsoft SQL Server.

















Just kidding.  0p3// 50ur<3 r00lz!!!111  

[Robert]

MSSQL....

PUKE!!


I have it running on the machine I'm on now. Its running in VMWare!

Bobby

Code:

INSERT INTO UserAgentSummary                   -- update UserAgentSummary table
SELECT UAYear = MAX(DATEPART(year, EventDateTime)),
       UAWeek = MAX(DATEPART(week, EventDateTime)),
       UAText = MAX(UserAgent),
       UACount = COUNT(UserAgent),
       UAHostIP = MAX(HostIP)
FROM Sessions
WHERE DATEDIFF(week, EventDateTime, GETDATE()) > 0
      AND UserAgent IS NOT NULL                -- ignore any records with no
      AND UserAgent <> ''                      -- value for UserAgent
GROUP BY UserAgent,
         DATEPART(year, EventDateTime),
         DATEPART(week, EventDateTime),
         HostIP
 
IF (@@ERROR <> 0) GOTO on_error                -- something went wrong

INSERT INTO RefererSummary                     -- update RefererSummary table
SELECT RefYear = MAX(DATEPART(year, EventDateTime)),
       RefWeek = MAX(DATEPART(week, EventDateTime)),
       /* Use a CASE statement to remove any query string from the URL */
       RefURL=MAX(
         CASE WHEN CHARINDEX('?', Referer) > 5
         THEN SUBSTRING(Referer, 1, CHARINDEX('?', Referer) - 1)
         ELSE Referer
         END),
       RefCount=COUNT(Referer),
       RefHostIP = MAX(HostIP)

[Tags:]

OrthodoxChristianity.net > General Forums > Board News > Topic: WHEW!