在MySql表中同时插入/更新？ [英] Insert/ update at the same time in a MySql table?

问题描述

我在Web服务器上托管了一个MySql数据库，该服务器上有一组包含数据的表。我正在分发使用HTML5 / Javascript / CS3构建的前端应用程序。

I have a MySql database hosted on a webserver which has a set of tables with data in it. I am distributing my front end application which is build using HTML5 / Javascript /CS3.

现在，当多个用户尝试在其中一个表中进行插入/更新时，它会同时产生冲突还是会自动为我处理表的锁定示例，例如，当一个用户正在使用时，它将为他锁定表，然后在用户完成操作后让其余用户进入队列。释放锁，然后将其交给队列中的下一个？这会发生还是我需要在mysql数据库中处理该情况？

Now when multiple users tries to make an insert/update into one of the tables at the same time is it going to create a conflict or will it handle the locking of the table for me automatically example when one user is using, it will lock the table for him and then let the rest follow in a queue once the user finishes it will release the lock and then give it to the next in the queue ? Is this going to happen or do i need to handle the case in mysql database

示例：

用户想要在数据库中进行插入，他调用了位于Web服务器上的php文件，该文件具有插入命令以将数据发布到数据库中。我担心两个或两个以上的人同时插入一个内容会进行更新。

When a user wants to make an insert into the database he calls a php file located on a webserver which has an insert command to post data into the database. I am concerned if two or more people make an insert at the same time will it make the update.

mysqli_query($con,"INSERT INTO cfv_postbusupdate (BusNumber, Direction, StopNames, Status, comments, username, dayofweek, time) VALUES (".trim($busnum).", '".trim($direction3)."', '".trim($stopname3)."', '".$status."', '".$comments."', '".$username."', '".trim($dayofweek3)."', '".trim($btime3)."' )");

推荐答案

MySQL自动处理表锁定。

MySQL handles table locking automatically.

请注意，使用MyISAM引擎，整个表将被锁定，并且语句将阻塞（排队），等待释放锁。

Note that with MyISAM engine, the entire table gets locked, and statements will block ("queue up") waiting for a lock to be released.

InnoDB引擎提供了更多的并发性，并且可以执行行级锁定，而不是锁定整个表。

The InnoDB engine provides more concurrency, and can do row level locking, rather than locking the entire table.

在某些情况下，您需要例如，如果您要维护引用完整性，并且要在会话运行时禁止其他会话对任何表进行更改，请对多个MyISAM表进行锁定。但是，这确实杀死了并发性。

There may be some cases where you want to take locks on multiple MyISAM tables, if you want to maintain referential integrity, for example, and you want to disallow other sessions from making changes to any of the tables while your session does its work. But, this really kills concurrency; this should be more of an "admin" type function, not really something a concurrent application should be doing.

如果您正在使用事务（InnoDB），则该问题应该更多地是管理类型的功能。您的应用程序需要处理的是表被锁定的行的顺序；当MySQL检测到有两个（或多个）事务无法进行，因为每个事务都需要获取彼此持有的锁时，应用程序可能会遇到死锁异常。 MySQL唯一能做的就是检测到这一点，而MySQL唯一能做的恢复就是选择其中一个事务作为受害者，因为该事务将获得死锁异常，因为MySQL杀死了它，从而允许至少要进行的交易之一。

If you are making use of transactions (InnoDB), the issue your application needs to deal with is the sequence in which rows in which tables are locked; it's possible for an application to experience "deadlock" exceptions, when MySQL detects that there are two (or more) transactions that can't proceed because each needs to obtain locks held by the other. The only thing MySQL can do is detect that, and the only recovery MySQL can do for this is to choose one of the transactions to be the victim, that's the transaction that will get the "deadlock" exception, because MySQL killed it, to allow at least one of the transactions to proceed.

这篇关于在MySql表中同时插入/更新？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！