mysql - 如何处理MySQL中的意外关闭以确保数据库一致性？

Question

我从数据库(MySQL)中获得了这个“tbluser”，其中存储了用户帐户信息，例如用户名和密码等。它还包含一个 bool(boolean) 值(类型TINYINT)列，如果用户登录，则 bool(boolean) 值列将变为value的“1”，然后注销后将其值设为“0”，这样我就可以轻松识别在线用户或未在线用户。

我的问题是，如果用户已经登录怎么办？则表示电源中断或异常关机。我希望该 bool(boolean) 列或该用户自动变为“0”，以便在已经通电时，该用户可以再次登录。

那可能吗？我在考虑ROLLBACK COMMIT，但也许有人有另一个主意？

Answer 1

您可以对用户是否登录进行某种控制的唯一方法是利用系统资源，我认为这是不必要的，但这是可行的。

您需要跟踪用户的空闲时间，这不一定意味着他们的电脑崩溃了，他们只是在一段时间内没有在您的应用程序内执行任何操作。如果您将我注销，我个人会感到不高兴，而我只是因为抽烟而不得不再次登录。

如果用户闲置，则可以注销他，将字段更改为0并强制他们重新登录。但是，如果用户由于某些或其他原因断电并且已经闲置，它将可以使用。

您可以在Stackoverflow中查看THIS链接。您还将从MSDN HERE中找到一个不错的代码示例，该示例说明了空闲时间跟踪。

如果该链接因将来的读者而出于某种原因而死亡，则此处是MSDN的摘录和代码-显示访问权限，但很容易转换为.net-

This topic shows how to create a procedure that will run if your Access application does not detect any user input for a specified period of time. It involves creating a hidden form, DetectIdleTime, which keeps track of idle time. Follow these steps to create the DetectIdleTime form.

Create a blank form that is not based on any table or query and name it DetectIdleTime. Set the following form properties:

属性

值
开机定时器
[ Activity 程序]
计时器间隔
1000 ##

Enter the following code for the OnTimer property event procedure:

Sub Form_Timer() 
     ' IDLEMINUTES determines how much idle time to wait for before 
     ' running the IdleTimeDetected subroutine. 
     Const IDLEMINUTES = 5 

     Static PrevControlName As String 
     Static PrevFormName As String 
     Static ExpiredTime 

     Dim ActiveFormName As String 
     Dim ActiveControlName As String 
     Dim ExpiredMinutes 

     On Error Resume Next 

     ' Get the active form and control name. 

     ActiveFormName = Screen.ActiveForm.Name 
     If Err Then 
        ActiveFormName = "No Active Form" 
        Err = 0 
     End If 

     ActiveControlName = Screen.ActiveControl.Name 
        If Err Then 
        ActiveControlName = "No Active Control" 
        Err = 0 
     End If 

     ' Record the current active names and reset ExpiredTime if: 
     '    1. They have not been recorded yet (code is running 
     '       for the first time). 
     '    2. The previous names are different than the current ones 
     '       (the user has done something different during the timer 
     '        interval). 
     If (PrevControlName = "") Or (PrevFormName = "") _ 
       Or (ActiveFormName <> PrevFormName) _ 
       Or (ActiveControlName <> PrevControlName) Then 
        PrevControlName = ActiveControlName 
        PrevFormName = ActiveFormName 
        ExpiredTime = 0 
     Else 
        ' ...otherwise the user was idle during the time interval, so 
        ' increment the total expired time. 
        ExpiredTime = ExpiredTime + Me.TimerInterval 
     End If 

     ' Does the total expired time exceed the IDLEMINUTES? 
     ExpiredMinutes = (ExpiredTime / 1000) / 60 
     If ExpiredMinutes >= IDLEMINUTES Then 
        ' ...if so, then reset the expired time to zero... 
        ExpiredTime = 0 
        ' ...and call the IdleTimeDetected subroutine. 
        IdleTimeDetected ExpiredMinutes 
     End If 
  End Sub

Create the following procedure in the form module:

Sub IdleTimeDetected(ExpiredMinutes) 
     Dim Msg As String 
     Msg = "No user activity detected in the last " 
     Msg = Msg & ExpiredMinutes & " minute(s)!" 
     MsgBox Msg, 48 
  End Sub