Mysql Learning Time And Locking

Mysql time related learning notes.

timestamp vs datetime range

the TIMESTAMP type’s range is ‘1970-01-01 00:00:01’ UTC to ‘2038-01-09 03:14:07’ UTC.
the DATETIME type’s range is ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’.

why I can set timestamp a invalid default value

That is because of server SQL Mode - NO_ZERO_DATE.
From the reference: NO_ZERO_DATE - In strict mode, don’t allow ‘0000-00-00’ as a valid date. You can still insert zero dates with the IGNORE option. When not in strict mode, the date is accepted but a warning is generated.

how to init timestamp and datatime

CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Mysql lock

Alter table and lock

alter table without locking table? create a new table and copy content to it, finally rename table for mysql online operation is not good enough to handle all DML operations.

mysql cluster DML and lock: As far as I can understand, cluster which have master-slave node, is more suitable for above tricks/toolkit.

Lock table for test transaction

When I want to test the transaction of spring, I have to make some exceptional situation to test roll back. One of way I can come up with is to lock a table to make sql query fail. Here is the detail:

Session A:

MariaDB [test]> lock table student write;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> select * from student;
+-----+----------+--------+-------+------------+----------+
| sid | name     | gender | birth | department | address  |
+-----+----------+--------+-------+------------+----------+
|   1 | xiaoming |      0 |  1998 | software   | jiangsu  |
|   2 | huahua   |      0 |  1996 | software   | jiangsu  |
|   3 | xiaocai  |      0 |  1995 | software   | jiangsu  |
|   4 | xiaofang |      1 |  1993 | history    | hunan    |
|   5 | hechao   |      1 |  1994 | math       | hunan    |
|   6 | chenshi  |      0 |  1996 | history    | jiangsu  |
|   7 | zhaoxiao |      1 |  1992 | math       | zhejiang |
|   8 | xiaojing |      1 |  1991 | software   | zhejiang |
+-----+----------+--------+-------+------------+----------+
8 rows in set (0.00 sec)

Another session B fails to read after session A lock the table:

MariaDB [test]> select * from student;
^CCtrl-C -- query killed. Continuing normally.
ERROR 1317 (70100): Query execution was interrupted
MariaDB [test]> select * from student;

when session A unlock table with following command

unlock tables;

another session get the query at once( with the locked time count in):

MariaDB [test]> select * from student;
+-----+----------+--------+-------+------------+----------+
| sid | name     | gender | birth | department | address  |
+-----+----------+--------+-------+------------+----------+
|   1 | xiaoming |      0 |  1998 | software   | jiangsu  |
|   2 | huahua   |      0 |  1996 | software   | jiangsu  |
|   3 | xiaocai  |      0 |  1995 | software   | jiangsu  |
|   4 | xiaofang |      1 |  1993 | history    | hunan    |
|   5 | hechao   |      1 |  1994 | math       | hunan    |
|   6 | chenshi  |      0 |  1996 | history    | jiangsu  |
|   7 | zhaoxiao |      1 |  1992 | math       | zhejiang |
|   8 | xiaojing |      1 |  1991 | software   | zhejiang |
+-----+----------+--------+-------+------------+----------+
8 rows in set (3.15 sec) <---- here

More

What should be noticed is that lock doesn’t mean transaction. The transaction provide ACID : a set of properties to make sure data integrity, while locking can only meet the atomicity requirement.

• Atomicity
  Atomicity requires that each transaction be “all or nothing”: if one part of the transaction fails, then the entire transaction fails, and the database state is left unchanged. An atomic system must guarantee atomicity in each and every situation, including power failures, errors, and crashes. To the outside world, a committed transaction appears (by its effects on the database) to be indivisible (“atomic”), and an aborted transaction does not happen.

And an example from wikipedia show isolation failure that locking can’t provide:

• Isolation
  The isolation property ensures that the concurrent execution of transactions results in a system state that would be obtained if transactions were executed serially, i.e., one after the other. Providing isolation is the main goal of concurrency control. Depending on the concurrency control method (i.e., if it uses strict - as opposed to relaxed - serializability), the effects of an incomplete transaction might not even be visible to another transaction.

Consider two transactions. T1 transfers 10 from A to B. T2 transfers 10 from B to A. Combined, there are four actions:
T1 subtracts 10 from A.
T1 adds 10 to B.
T2 subtracts 10 from B.
T2 adds 10 to A.
If these operations are performed in order, isolation is maintained, although T2 must wait. Consider what happens if T1 fails half-way through. The database eliminates T1’s effects, and T2 sees only valid data.

Ref

• locking vs transaction

Written with StackEdit.