DEV Community: Aldora

Online Tool: sql schema to entity

Aldora — Tue, 09 Aug 2022 05:08:00 +0000

https://converter.aldoraweb.com: It converts the sql table to entity, saves your time

This is my first site, built with Nextjs.
It can convert mysql schema to entity(something I need while work)
Enjoy it~
Looking forward to feedback.

My new site: https://www.aldoraweb.com

Aldora — Thu, 09 Jun 2022 03:08:39 +0000

I'm building my own site with ghost: https://www.aldoraweb.com
Welcome~

Common Devices CSS Width

Aldora — Mon, 14 Feb 2022 08:29:58 +0000

*source

Device resolution is not css resolution

Aldora — Mon, 14 Feb 2022 07:27:50 +0000

For more info: click here

MySql: Locks on (not unique)Index in Repeatable Read Transaction

Aldora — Tue, 25 Jan 2022 08:01:17 +0000

* For MySql 8(the version this article uses), we can view locks in table: performance_schema.data_locks. For versions prior to version 8, they are in
table information_schema.innodb_locks.

Key point: Gap lock would be created. Be careful, a gap, not just the index you are searching

For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it;
For other search conditions(not unique index), InnoDB locks the index range scanned, using gap locks or next-key locks to block insertions by other sessions into the gaps covered by the range.(source)

For example:

We create payment table, as following image shows. Column state is a normal index.

id	description	state	customer_id
1	payment1	102	1
2	payment2	100	1
3	payment3	104	2
4	payment4	106	2
5	payment3	104	1

Example1

query:

  START TRANSACTION;
  SELECT * FROM payment WHERE state = 104 FOR UPDATE;(or UPDATE payment SET description = "updated from console" WHERE state = 104)
  SELECT OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;
  COMMIT ;

lock from performance_schema.data_locks:

ENGINE	OBJECT_NAME	INDEX_NAME	LOCK_TYPE	LOCK_MODE	LOCK_STATUS	LOCK_DATA
INNODB	payment	NULL	TABLE	IX	GRANTED	NULL
INNODB	payment	state_index	RECORD	X	GRANTED	104, 3
INNODB	payment	state_index	RECORD	X	GRANTED	104, 5
INNODB	payment	PRIMARY	RECORD	X,REC_NOT_GAP	GRANTED	3
INNODB	payment	PRIMARY	RECORD	X,REC_NOT_GAP	GRANTED	5
INNODB	payment	state_index	RECORD	X,GAP	GRANTED	106, 4

locks break down:
- MySql add row locks on id=3, id=5 as they have 104 state, which means others transactions can't update them.
- Mysql not only locks rows with state 104, it also crates a gap lock between state 104 and its next value(106, not inclusive) in the table, and it means insertion with state less than 106, greater than 104 would fail. For example, an insertion with state 105 would fail, but with state 106 would succeed.
- IX- intention lock, table-level locks that indicate which type of lock (shared or exclusive) a transaction requires later for a row in a table. click here for more > The intention locking protocol is as follows: > > Before a transaction can acquire a shared lock on a row in a table, it must first acquire an IS lock or stronger on the table. > > Before a transaction can acquire an exclusive lock on a row in a table, it must first acquire an IX lock on the table.
- X- exclusive lock, permits the transaction that holds the lock to update or delete a row. Click here for more
- REC_NOT_GAP indicates a record-only lock, and means the S lock is a record lock.
- GAP: gap lock, a gap between index records, or a lock on the gap before the first or after the last index record. It should be noted that the row marked as gap lock is not included as lock, so it means it still can be updated or insert.

Example2

query:

  START TRANSACTION;
  SELECT * FROM payment WHERE state = 106 FOR UPDATE;(or UPDATE payment SET description = "updated from console" WHERE state = 106)
  SELECT OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;
  COMMIT ;

lock from performance_schema.data_locks:

ENGINE	OBJECT_NAME	INDEX_NAME	LOCK_TYPE	LOCK_MODE	LOCK_STATUS	LOCK_DATA
INNODB	payment	NULL	TABLE	IX	GRANTED	NULL
INNODB	payment	state_index	RECORD	X	GRANTED	supremum pseudo-record
INNODB	payment	state_index	RECORD	X	GRANTED	106, 4
INNODB	payment	PRIMARY	RECORD	X,REC_NOT_GAP	GRANTED	4

locks break down:
- The SELECT FOR UPDATE is locking between 106 and the next value in the payment table. Since there is no next-value, it is locking until the supremum pseudo-record. In the above example, any value above 106 can't be inserted or updated.

Related Materials:

Book :Textbook for System Design Interviews

Aldora — Wed, 12 Jan 2022 05:40:00 +0000

Recomendation: Textbook for System Design Interviews: https://systemdesign.org

The more I learn Java, the more I feel that feel how loose PHP is.

Aldora — Fri, 07 May 2021 05:12:12 +0000

the common feature of java communities is their UI sucks

Aldora — Sat, 10 Apr 2021 07:52:33 +0000

What features to include in my java project

Aldora — Tue, 06 Apr 2021 04:33:13 +0000

I plan to switch from php to java, and in order to find a job as java programmer, I plan to write a project to show that I'm capable to write with java.
My question is what kind of features I should include in the project, such as basic database operations, basic deployment?

Use Curl to diagnose slow network

Aldora — Fri, 16 Oct 2020 16:31:42 +0000

article recomendation:
A Question of Timing

PHP Child Process and Signal-Part 2

Aldora — Mon, 12 Oct 2020 16:51:53 +0000

When the signal of forked process doesn't get successful process from pcntl_wait(), it would stay untils it get processed successfully. (Once gets handled, it would not work for later pcntl_wait())

In the part 1, I talked about what happens when we kill parent process, then kill the child. In this part, I would talk about what would happen if we kill the process in reverse order.

pcntl_async_signals(true);

pcntl_signal(SIGTERM, function ($signal, $status) {
    echo "inside pcntl_signal\n";
    $pid = pcntl_wait($processStatus, WUNTRACED);
//    $pid = pcntl_wait($processStatus, WNOHANG);

    if (pcntl_wifexited($processStatus)) {
        $code = pcntl_wexitstatus($processStatus);
        print "pid $pid returned exit code: $code\n ";
    } else {
        print "$pid was unnaturally terminated\n ";
    }
//    print_r($processStatus);
//    echo "\n";
//    echo "signal: $signal\n";
//    print_r($status);
});
if ($pid = pcntl_fork()) {
    $parent = getmypid();
    echo "parent: $parent\n";

    sleep(60);
} else {
    $childPid = getmypid();
    echo "child pid: $childPid\n";

    sleep(60);
}

Execute the above script, and according to the output of process pid, first use kill command to kill parent process, then kill child process. The total output would be like this:

parent: <parent pid>
child pid: <child pid>
inside pcntl_signal
pid -1 returned exit code: 0
inside pcntl_signal
pid <child pid> returned exit code: 0

We still create two processes, one parent, one child. Kill the child, and this invokes the signal handler in the child process. However, pcntl_wait() only works when it's called in parent, so it returns -1. Then, we kill the parent, this time, the pcntl_wait() in parent handles the child process status info successfully. But this raises a noticeble point: the signal which can't be handled by pcntl_wait() in child process stays until successfully dealed by parent process.

How can we build developer network?

Aldora — Mon, 12 Oct 2020 04:48:43 +0000

In addition to building up our technical skills as developers, how can we also build up our developers network besides through official work?