Shulou(Shulou.com)06/01 Report--

This article is mainly about the difference and usage of MySQL and Redis transactions. If you are interested, let's take a look at this article. I believe it is of some reference value to you after reading the differences and usage of MySQL and Redis transactions.

Redis

[1] Redis transactions can execute more than one command at a time with the following two important guarantees:

Bulk operations are cached in queues before sending EXEC commands. After receiving the EXEC command, enter the transaction execution, any command execution in the transaction fails, and the rest of the commands are still executed. During transaction execution, command requests submitted by other clients are not inserted into the transaction execution command sequence.

A transaction goes through the following three stages from start to execution:

Start the business. Order to join the team. Execute the transaction.

The execution of a single Redis command is atomic, but Redis does not add any mechanism to maintain atomicity on the transaction, so the execution of the Redis transaction is not atomic.

A transaction can be understood as a packaged batch execution script, but a batch instruction is not an atomized operation, and the failure of an intermediate instruction will not cause a rollback of the previously done instruction, nor will it cause subsequent instructions not to be done.

Operation error

It seems to be a little tongue-twisting, so let's actually implement it and take a look at the results.

127.0.0.1 multiOK127.0.0.1:6379 > set tr_1 233QUEUED127.0.0.1:6379 > lpush tr_1 666QUEUED127.0.0.1:6379 > set tr_2 888QUEUED127.0.0.1:6379 > exec1) OK2) (error) WRONGTYPE Operation against a key holding the wrong kind of value3) OK

In the above transaction, we set a string data with key to tr_1, and then add elements through lpush, which is obviously the wrong way to operate. When we submit a transaction, we have an operation error, so let's see what the value of tr_1 is.

127.0.0.1 6379 > get tr_1 "233"

The tr_1 content from the get command is still 233, and it hasn't changed, so take a look at the others.

127.0.0.1 6379 > keys * 1) "tr_2" 2) "tr_1" 127.0.0.1 tr_1 > get tr_2 "888" 127.0.0.1

Here we can see that tr_2 exists and the value is printed. At this time, we find that even if an operation error occurs, the error does not stop the execution, and the statement after the error is executed and executed successfully. It seems that the failure of one of the instructions mentioned above will not lead to the rollback of the previous instruction, nor will it cause the subsequent instruction not to do.

Grammatical error

There is another situation of syntax error in NO~, at this time.

127.0.1 set' command127.0.0.1:6379 6379 > multiOK127.0.0.1:6379 > set tr_1 233QUEUED127.0.0.1:6379 > lpush tr_1 666QUEUED127.0.0.1:6379 > set (error) ERR wrong number of arguments for 'set' command127.0.0.1:6379 > set 233 (error) ERR wrong number of arguments for' set' command127.0.0.1:6379 > set tr_2 888QUEUED127.0.0.1:6379 > exec (error) EXECABORT Transaction discarded because of previous errors.127.0.0.1:6379 > keys * (empty list or set)

We didn't give any parameters when we executed the set, but we deliberately missed one parameter the second time. You can see that the syntax error was reported, and the transaction was finally committed, which also told us that the transaction was lost because of the error, and then searched with keys * and found that this was true.

Document interpretation

Here can be mentioned in the official document

Errors inside a transaction

/ / two kinds of command errors may be encountered during execution.

During a transaction it is possible to encounter two kind of command errors:

/ / 1. Commands cannot enter the queue, such as incorrect number of parameters, incorrect command name, or some key errors such as insufficient memory

A command may fail to be queued, so there may be an error before EXEC is called. For instance the command may be syntactically wrong (wrong number of arguments, wrong command name,...), or there may be some critical condition like an out of memory condition (if the server is configured to have a memory limit using the maxmemorydirective).

/ / 2. Do wrong operations on keys, such as using lpush on strings above

A command may fail after EXEC is called, for instance since we performed an operation against a key with the wrong value (like calling a list operation against a string value).

/ / the client checks the typed command and most of the time, it will find the first type of error before calling exec. If the command execution returns QUEUED, the command will enter the queue normally, otherwise the error will occur. In most cases, the client will terminate and abandon the transaction.

Clients used to sense the first kind of errors, happening before the EXEC call, by checking the return value of the queued command: if the command replies with QUEUED it was queued correctly, otherwise Redis returns an error. If there is an error while queueing a command, most clients will abort the transaction discarding it.

With regard to Redis, we will see here for a while and then we will see MySQL.

MySQL

As we all know, only InnoDB engine supports transactions in MySQL. Autocommit needs to be disabled before MySQL transactions are enabled.

Test table structure user column type annotation idint (11) automatic increment primary key IDmoneyint (11) [0] money titlevarchar (500) NULL address

Here to simulate a transfer operation: a to B transfer 100 yuan.

Step analysis A hundred yuan, B-100 yuan, that is, two steps although very simple, simple to follow the process.

As you can see, there is no problem, so we artificially create some problems from it?

Operation error

Column type annotation idint (11) auto increment

Moneyint (11) unsigned [0]

Titlevarchar (500) NULL

Here we make the money field unsigned, that is, it cannot be less than 0, and adjust the data in the database as follows.

`SELECT * FROM `user`LIMIT 50` (0.000 seconds) modify idmoneytitle edit 110000A edit 20B

Then execute the following SQL

Select version (); SET AUTOCOMMIT=0;begin;select * from user where title in for update;update user set money = money + 1000 where title = 'Achievement update user set money = money-1000 where title =' breadth alternative select * from user where title in ('Amalagery B'); commit

The problem occurs, and an error is reported here, but you can see that the previous SQL has already been executed, and the result has changed. From this point of view, it seems to be similar to the processing of Redis, except that the statement continues to execute after the error. But the value note is that in our actual development, the program will directly throw an exception so that we can perform rollback in the catch block to ensure the integrity of the data by rollback operation. Even if we use the MySQL command line alone, we can use stored procedures to roll back the exception.

Grammatical error

I just saw that Redis will automatically discard transactions and prevent commit when it encounters syntax errors. What about MySQL?

Answer: no, when MySQL executes sequentially, if the exception is not handled, it will always commit the successful execution without triggering automatic termination, but we can abandon the submission when the program is executed.

Why didn't Redis roll back?

The official documentation of Redis gives this explanation.

/ / Redis commands fail only when called with the wrong syntax (and the problem cannot be detected during the command queue), or for keys that hold the wrong data type, the Redis command may fail: this means that the command that actually failed is the result of a programming error and an error that is likely to be detected during development, not in production.

Redis commands can fail only if called with a wrong syntax (and the problem is not detectable during the command queueing), or against keys holding the wrong data type: this means that in practical terms a failing command is the result of a programming errors, and a kind of error that is very likely to be detected during development, and not in production.

/ / Redis is internally simplified and faster because it does not require the ability to roll back.

Redis is internally simplified and faster because it does not need the ability to roll back.

Does the above details about the differences and usage of MySQL and Redis transactions help you? If you want to know more about it, you can continue to follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.