How Java implements row locks on MySQL databases

The scenario is as follows:There is a balance in the user account and the balance needs to be updated in real time when a transaction occurs. If there is a concurrency problem, then the user balance and the actual transaction will be inconsistent, which is very dangerous for the company and the customer. so how to avoid:Online Search, there are the following two ways:1. Use pessimistic lockWhen you need to change the balance, set the for update row lock for the records in the transaction that currently need to be updated by code, and then start the normal query and update operationsIn this way, other transactions can only wait for the transaction to complete before they can be manipulatednote, of course, that if you use spring's transaction annotations, you need to configure:

    <!--(transaction Management) transaction manager, use Jtatransactionmanager for global TX -      <BeanID= "TransactionManager"class= "Org.springframework.jdbc.datasource.DataSourceTransactionManager">          < Propertyname= "DataSource"ref= "DataSource" />      </Bean>    <!--defining transactions using annotation -     <Tx:annotation-drivenTransaction-manager= "TransactionManager" />

Add a transaction note at the specified code point

@Transactional @Override Public BooleanIncreasebalancebylock (Long userId, BigDecimal amount)throwsvalidateexception {LongTime =System.currenttimemillis (); //get a lock on a recordUserbalance balance =Userbalancedao.getlock (userId); Logger.info ("[Lock] start. Time: {} ", time); if(NULL==balance) {            Throw Newvalidateexception (Validateerrorcode.errorcode_balance_notexist,"User balance is not exist"); }        Booleanresult =Userbalancedao.increasebalancebylock (balance, amount); LongTimeend =System.currenttimemillis (); Logger.info ([Lock] End. Time: {} ", Timeend); returnresult; }

MyBatis in the lock mode, the actual test of the method can be effectively controlled, but in the case of large concurrency, there may be performance problems it

  <id= "Getlock"  resultmap= "Baseresultmap"  parametertype  = "Java.lang.Long">        <![ cdata[            select * from User_balance where id=#{id,jdbctype=bigint} for update;         ]]>    </Select>

2. Use optimistic lockThis approach also solves the problem described in the scenario (which I think is more appropriate for infrequent operations):When designing the table, add a version (versioning field), each time you need to update the balance, the first to get the object, update the time according to version and ID to be updated, if the number of updates back to 0, indicating that version has changedyou need to repeat the update operation as follows: SQL Script            

Update Set = #{balance,jdbctype=DECIMAL= Version+1where= #{id,jdbctype=BIGINTand= #{version,jdbctype=  BIGINT}

This is a way of not using database locks, and the solution is clever. Of course, in the case of a large number of concurrent cases, a deduction needs to repeat multiple operations to succeed, or there are shortcomings. I don't know if there's any better way.

How Java implements row locks on MySQL databases